derived date format

Question

Hello,

In the ADF dataflow derived column, I would like to convert a date from

the format: dd-MMM-yy to MM/dd/yy

existing date format is like: 23-Mar-24

Therefore I use the following expression in the derived column

toDate(columnName, 'dd-MMM-yy', 'MM/dd/yy')

but the result I see is: 2024-03-23

I would like the date to show as 3/23/24

thank you

Answer 1

@arkiboys

Thanks for reaching out to Microsoft Q&A

The reason you're seeing "2024-03-23" instead of "3/23/24" is because the toDate function in ADF Dataflow converts the string to a date type, and by default, it doesn't change the format of the output.

Here's how you can achieve the desired format "MM/dd/yy":

1. Convert to Date: Use the toDate function as you have done to convert the string to an actual date type. This ensures accurate date calculations and comparisons later in your data flow.

Format the Date: After converting to a date, use the toString function to format the date according to your preference. In your case, use the following expression:

toString(toDate(columnName, 'dd-MMM-yy'), 'MM/dd/yy')

This expression first converts the columnName to a date using the format "dd-MMM-yy" and then converts the resulting date object to a string in the format "MM/dd/yy".

Example:

If your columnName contains the value "23-Mar-24", the expression will produce the following output:

toString(toDate("23-Mar-24", 'dd-MMM-yy'), 'MM/dd/yy') = "03/23/24"

This will give you the desired format "MM/dd/yy".

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.