good morning,
I'm working on a datalake that retrieves data from sqlserver, transforms it with dbt and then inserts it into postgresql.
The format of my source data has changed (the software editor has made changes in sqlserver) and I now have time datetime2 (0) or datetime2 (7) instead of datetime. Since this change, dbt has been giving me errors for each date column that was previously inserted as a timestamp, see first screen : invalid input syntax for type date: "Mar 17 2021 08:21:19:0000000AM".
Especially since in my sqlserver table the writing is not the same as the insertion in postgresql. In sqlserver I have YYYY-MM-DD HH:MM:SS.FFFFFFF instead of the month on 3 letters in postgresql visible in the error. I have to cast in varchar to avoid insertion errors, but then I can't calculate durations (previously just end time - start time). I'm looking for a way to cleanly cast the displayed format of datetime(2) to timestamp
I tried --TO_TIMESTAMP("date_debut", 'YYYY-MM-DD HH:MM:SS') and i get invalid value "Oct " for "YYYY"
and date_debut::timestamp
Any suggestions?
The format is not correct, you mention the ISO format but that will be the output from to_timestamp() anyway. You should mention how your input looks like. This one works for me:
SELECT to_timestamp('Mar 17 2021 08:21:19:0000000AM'
,'Mon DD YYYY HH12:MI:SS:USAM');