SnapLogic DateTime Conversion Guidelines
The SnapLogic Elastic Integration Platform native DATETIME data type is time zone-aware. For service endpoints that do not expose data types that are not time zone-aware, the SnapLogic Expression Language can be used to easily convert to supported 3rd party date, datetime, time, and timestamp data types. The matrix below provides sample conversions between the SnapLogic DATETIME variable, $snapDateTime , and several known 3rd party data types normalized to the US/Pacific time zone. NOTE: For 3rd party DATETIME / TIMESTAMP data types that do not support time zone, the samples assume that these values are being stored normalized to the UTC (Coordinated Universal Time) time zone. In addition, links to reference documentation for 3rd party data types have been provided where applicable. Service/Endpoint Data Type Sample Conversion Expression Amazon Redshift DATE LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’)) Amazon Redshift TIMESTAMP 1 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’)) MongoDB DATETIME 3 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"US/Pacific”}’)) MySQL DATE LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’)) MySQL DATETIME 1 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’)) MySQL TIME 1 LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"US/Pacific”}’)) MySQL TIMESTAMP 2 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"US/Pacific”}’)) Oracle DATE LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’)) Oracle TIMESTAMP 1 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’)) Oracle TIMESTAMP WITH TIME ZONE 3 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"US/Pacific”}’)) Oracle TIMESTAMP WITH LOCAL TIME ZONE LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"US/Pacific”}’)) PostgreSQL DATE LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’)) PostgreSQL TIME 1 LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"UTC”}’)) PostgreSQL TIME WITH TIME ZONE 3 LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"US/Pacific”}’)) PostgreSQL TIMESTAMP 1 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’)) PostgreSQL TIMESTAMP WITH TIME ZONE 3 LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"US/Pacific”}’)) SAP HANA DATE LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’)) SAP HANA TIME LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"US/Pacific”}’)) SAP HANA SECONDDATE LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’)) SAP HANA TIMESTAMP 1 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’)) Salesforce DATE LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’)) Salesforce DATETIME 1 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’)) Salesforce TIME LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"US/Pacific”}’)) SQL Server DATETIME 1 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’)) SQL Server DATETIME2 1 LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’)) 1 Does not provide time zone support. If provided, time zone offset will be stripped. 2 Supports time zone. Time zone is not stored interally. By default, on a SELECT, the value is normalized to the time zone set for the user’s local session. 3 Supports time zone. Also see Database Data Types in the documentation.
Cannot format date correctly to YYYY-MM-DD
Hi there, I’ve looked at other articles addressing this but non has helped me so far. Im looking to format a date in the DD/MM/YYYY to YYYY-MM-DD. The date will always be populated. I’ve tried the following expressions but they wont process correctly Date.parse($original[‘Due Date’], “yyyy MM dd”) LocalDate.parse($original[‘Due Date’],“yyyy/MM/dd”) If I use the above expression and if the date is 06/04/2021, the output date becomes 0006-04-18. It’s inexplicable.
How to get the file name(s) from a Multi File Reader
I have a Multi File Reader reading a series of S3 files using a wildcard, and writing the data to Snowflake. There is a Mapper in between. Functionally, everything is working as expected. I’d like to get the name of the file in which the data was read, and write it to a Meta_FileName column. How do I retrieve the file name from the Multi File Reader? I’m sort of assuming it’s an expression to be added in the Mapper but not sure. TIA!
