Date formatting
Hi ,
I am trying to convert a local-date-time format to dd-MMM-yy ( say for eg :13-NOV-19).
I tried various methods still could not achieve this . It could be useful if I have a method to achieve this
Thanks in advance
Forum Discussion
11 Replies
From what I’m seeing you can use dd-MMM-yy, for example, Date.now().toLocaleDateString({“format”:“dd-MMM-yy”}) returns “27-Jul-20”.
I don’t think you Timestamp value is in one of the standard formats that we typically parse (See Parsing a Date on the Expression Language Examples page of our documentation), so you may need to follow the parsing a non-standard example of Date.parse($NonStandardDate, “dd-MMM-yy”)
Is Timestamp showing as a date-time field or a string? If it’s being treated as a string, then some other things will need to be done first.
Hi Dmiller,
Hope you are fine . Thanks for the reply my incoming date is of local-date time type . Even after using the above condition Date.now().toLocaleDateString({“format”:“dd-MMM-yy”}) it returns the value as 2020-07-27 instead of 27-JUL-20 . Please advise on the same
Hi Harsha,
Good day, since the IF_ROW_PROCSSED_DT is already a Date data type you can directly invoke the method in the object
e,g,
$IF_ROW_PROCSSED_DT.toLocaleDateString({“format”:“yy-MMM-dd”})
FYI, my sample data type is date-time if error “method doesn’t exists” on your end… try
Date.parse($IF_ROW_PROCSSED_DT.toString()).toLocaleDateString({“format”:“yy-MMM-dd”})
Thanks,
EmEm
Dev recommended trying: Date.now().toLocaleDateTimeString({“format”:“dd-MMM-yy”}) instead.
Sorry to raise this up again, but this is the only example I could find that might be the same thing I’m running into.
Can someone explain to me why a field with local-date-time is coming in as a json with a Timestamp key as shown below? Then when I try to build an expression to parse out only what I need, the result is correct, but running preview continually returns “null”. Or if null-safe access is off it will give me an error like it can’t evaluate .Timestamp.
Hi Mellisa,
Good day, I think there is no method .toString for Date object type, you can try Date.parse($LAST_DDD.timestamp).toLocaleDateString({‘format’:‘yyyy-MM-dd’})
Thanks,
EmEmThank you for the response.
Unfortunately, even trying to reference “.timestamp” of any kind, I get an error that it can’t be evaluated. Now it’s in the hands of snaplogic support. As an ugly workaround, the formatting of .timestamp can be left alone and instead write the entire results to a file/json and then read back in and manipulated, but we shouldn’t have to do that.
Well according to support, the snowflake Timestamp_NTZ is not compatible with snaplogic expression language. Only work around is to use a string field instead if possibe. If not in the database, then write it out to a file without manipulation and then read it in as a string in snaplogic. Not ideal, but I think candidate for snaplogic enhancement request.
Thanks again for the help.
As an alternative to writing/reading a JSON file, try this (or similar expression as-needed) in your mapper using the JSON parse() and stringify() in combination:
JSON.parse(JSON.stringify($LAST_RUN_DATE))
or
JSON.parse(JSON.stringify($LAST_RUN_DATE.Timestamp))While I don’t have access to the Snowflake Timestamp_NTZ type to test, I’ve tested this on a SQL Server DateTime2 field (which is also an incompatible local-date-time) and it converts its value to a string type.
Thanks for this suggestion.
One thing I uncovered, and I can’t believe it wasn’t mentioned to me, but was on the Snowflake Select snap, the Handle Timestamp and Date Time Data option. I hadn’t seen that and when I selected the SnapLogic Date Time format in Regional Time Zone option, the $LAST_RUN_DATE field would come as a single field and not put the .Timestamp in a sub-object. Ugh. This was a painful lesson.
