derived date format

arkiboys 9,666 Reputation points
2024-04-22T05:45:27.0333333+00:00

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,771 questions
0 comments No comments
{count} votes

Accepted answer
  1. phemanth 6,810 Reputation points Microsoft Vendor
    2024-04-22T06:18:03.3633333+00:00

    @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.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful