Ucanaccess inserting dates

[Jonathan Valdivieso]

Hi, I recently switched to using ucanaccess from an a jdbc:odbc driver. All works well now, but was wondering why The inserted value in my access database is different than my query. I ran the query separately in MS Access, and seemed to insert fine, but from the java application, the date seems to be inserted as 18 - Nov - 1111 instead of 11- Nov -1111. Another date example. 12/12/1212 is inserted as 19 - Dec - 1212 vs 12 - Dec 1212. I used this date to test, and more recent dates (>1990) are inserted accurately. Is it due to MS Access and the issue with dates prior to 1900? Below is my query string, and a shortened code I ran to insert.

Other dates I've tried and that inserted correctly are #11/11/1911# and #11/11/1811#

conn = DriverManager.getConnection(connectionName);

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

querystring =
Insert into example_table (f_c_id, f_date, f_begin, f_end, f_flag_begin, f_flag_end, start_number, end_number, b_rec,a_rec, start_Time, end_Time,f_duration) values ('AB',#11/11/1111#,#6:39:00#,#12:52:00#,0,0,1,26,'First','Second',#6:30:00#,#12:45:00#,373)

stmt.executeUpdate(queryString);
conn.close()

Thank You

[Gord Thompson]

Are you using an older version of UCanAccess? There was a very similar issue reported here that was supposed to have been fixed in UCanAccess 3.0.3.

[Jonathan Valdivieso]

Hi Gord, I am using UCanAccess 4.0.2, and recently updated Jackcess to 2.1.9. Thanks for linking the similar issue. So any year after 1582, the date is inserted correctly?

[Gord Thompson]

Dates after the start of the Gregorian calendar (circa 1582) should be okay, but the question is whether the earlier dates are being stored incorrectly or whether they are just being displayed incorrectly after you retrieve them. If you run a query that retrieves CStr(DateTimeField) instead of just DateTimeField do you see the correct date?

[Jonathan Valdivieso]

I believe they are being stored incorrectly, since they appear that way in Access. I've ran the same query in Access and the correct dates were inserted. I will try to run and retrieve them through the Java app, and report back.

[Gord Thompson]

I am unable to reproduce your issue. When I start with a new, empty .accdb file and do ...

C:\Users\Public\Downloads\UCanAccess>console.bat
Please, enter the full path to the access file (.mdb or .accdb): C:\Users\Public\test1111.accdb
Loaded Tables:

Loaded Queries:

Loaded Procedures:

Loaded Indexes:

UCanAccess>
Copyright (c) 2017 Marco Amadei
UCanAccess version 4.0.2
You are connected!!
Type quit to exit

Commands end with ;

Use:
   export [--help] [--bom] [-d <delimiter>] [-t <table>] [--big_query_schema <pathToSchemaFile>] [--newlines] <pathToCsv>;
for exporting the result set from the last executed query or a specific table into a .csv file

UCanAccess>CREATE TABLE example_table (id LONG PRIMARY KEY, f_date DATETIME);
UCanAccess>No rows affected

UCanAccess>INSERT INTO example_table (id, f_date) VALUES (1, #1111-11-11#);
UCanAccess>1 row(s) affected

UCanAccess>quit
Cheers! Thank you for using the UCanAccess JDBC Driver.
Press any key to continue . . .

C:\Users\Public\Downloads\UCanAccess>

 
... when I open the database in Access I see

[Jonathan Valdivieso]

Thanks for the help! Could it be due to file type? Access Database extention I am using is .mdb.