Good Evening,
This is a followup on the post Processing of dates from sdbc:flat connection.
Large number of the CSV inputs have timestamped data in DD/MM/YYY HH:MM:SS format and read as ResultSet. Is there a way to use the Options->Language Settings->Languages->Date acceptance patterns to allow for timestamp or date time patterns?
Thank you
Choose anything English but not US English.
CSV import into Base does not use any date acceptance patterns.
Its not exactly smart to use »Y-D-M HH:NN:SS« as acceptance-pattern ?!
How does it make a difference with sdbc:flat?
Can you please explain why?
For some reason when i did not add hours:minutes:seconds as part of the acceptance pattern it interpreted such date format as varchar
I created 3 text files with ISO, English and German time stamps.
Timestamp
2023-11-03 20:36:55
Timestamp
3/112023 20:36:55
Timestamp
3.11.2023 20:36:55
and connected a Base document to the csv directory.
I added one query for each table with different table name in the FROM clause:
SELECT "Timestamp", YEAR( "Timestamp" ) "Y", MONTH( "Timestamp" ) "M", DAYOFMONTH( "Timestamp" ) "D"
, HOUR( "Timestamp" ) "H", MINUTE( "Timestamp" ) "MIN", SECOND( "Timestamp" ) "SEC"
FROM "ISO"
When the global locale setting (above the acceptance pattern) is German(Germany), the UK query fails to interprete the date string.
When the global locale setting (above the acceptance pattern) is English(UK), the German query fails to interprete the date string.
The query on the ISO table works in any case.
After changing the global locale, the database document needs to be closed and reopened!
Date acceptance patterns with or without time portions make no difference.
i will check this in more detail