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.

Hello @NAl,

You can format the date with the following function:

Date.parse($original[‘Due Date’]).toLocaleDateString({“format”:“yyyy-MM-dd”})

image

Regards,

Bojan

1 Like

I’m afraid that’s throwing up an error:

Please check for syntax errors. As I can see from the suggestions, your date field from the incoming data comes directly as a $[‘Due Date’] and not $original[‘Due Date’]

1 Like

The second argument to parse is the format that the string is expected to be in and that’s why you get the 0006-04-18 result.

Here’s an example pipeline to try out. It includes a mapper with the expression @bojanvelevski mentioned and also another method using the Date Time Extractor snap from the ML Data Preparation snap pack.

date-example_2021_04_09.slp (7.0 KB)

How do I open the sample pipeline in Snaplogic? The file downloads to my desktop and cannot find the appropriate application to open it. I’m a newbie with SnapLogic so still trying to find my feet.

You should use import option from designer to import this sample pipeline directly to your Snaplogic.

@NAl

Click on “Import a Pipeline” button, and then open/select the appropriate sample pipeline that you want to import.

Regards,
Spiro Taleski

Thank you Spiro, found it

Okay so the Date/Time Extractor is not available in our packs which is fine.

Using the expression provided, the date still doesn’t format properly.
The input date is 06/04/2021 but in the expression appears as 0021-06-04

I think the problem is that, ‘Due Date’ is passed down the flow already as a date with a dd/MM/yy format (two digit year - 21), so when you add your preffered format , yyyy-MM-dd it adds two 0’s in front - 0021. If that’s the case, you can try to add ‘20’ manualy before you parse and format the date.

image

image

Note that this won’t help you if your process has older dates, before 2000, but if I am right about the issue, than we are close to the solution.

Hi @NAl,

You can try this solution if there are no dates who are in 2000’s and are larger than the current year.

$Date comes in format “dd/mm/yy” and is type of string.

$Date.split('/')[2].length <= 2 ? Date.parse($Date).plusYears(Date.now().getFullYear() - $Date.split('/')[2] >= 2000 ? 2000 : 1900).toLocaleDateString({format:"YYYY-MM-dd"}) : Date.parse($Date).toLocaleDateString({format:"YYYY-MM-dd"})

If the year in $Date is larger then the current one that means is not 2000’s and will set as it is from 1900’s.

If it’s year with all 4 digits it will format as it is, otherwise if it’s 1 or 2 digits it will add the century.

Regards,
Viktor Nedanovski

2 Likes

image
Check the characters used here, probably explains the error you have, copying from formatted web pages like this forum will give you wrong chars.

In the forum here, use the preformatted text when pasting code strings, and there will be no issues when copy/pasting. =)

You can also use formatting in Date.parse if needed to clarify the format of the input to parse.

Date.parse($original['Due Date'], "dd/MM/yyyy").toLocaleDateString({"format":"yyyy-MM-dd"})

1 Like