DateTime conversions in Azure Data Factory

Are you finding it difficult to operate on dates conversion in the Azure data factory? You may have googled but didn’t get the help then you have landed up to the very right place. In this post, I will take you through all the variety of data time conversions in various formats. Let’s understand in detail how you can convert string to DateTime format vice versa and many other transformations.

How to get Current DateTime in the Azure Data Factory (ADF)

To get the current date time in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow()

Result : “2021-09-01T21:00:00.0000000Z”

You can also give format as well ‘D’ which will return the date with Day.

utcNow('D')

Result : “Wednesday, September 01, 2021”

How to get Current Date in the Azure Data Factory (ADF)

To get the current date time in the Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('d')

Result : “9/1/2021”

You can also give format as well ‘D’ which will return the date with Day.

utcNow('D')

Result : “Wednesday, September 01, 2021”

How to get current day in the Azure Data Factory (ADF)

To get the current day (The full name of the day of the week) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('dddd')

Result : “Wednesday”

To get the current day (The abbreviate name of the day of the week) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('ddd')

Result : “Wed”

To get the current day (The day of the month, from 01 through 31.) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('dd')

Result : “01”

To get the current day (The day of the month, from 1 through 31.) in azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('d')

Result : “1”

How to get the current month in the Azure Data Factory (ADF)

To get the current month (The full name of the month) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('MMMM')

Result : ” September “

To get the current month (The abbreviated name of the month) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('MMM')

Result : “Sep”

To get the current month (The month, from 01 through 12) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('MM')

Result : “09”

To get the current month(The month, from 1 through 12) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('M')

Result : “9”

How to get the current year in the Azure Data Factory (ADF)

To get the current year (The year as a five-digit number) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('yyyyy')

Result : ” 02021″

To get the current year (The year as a four-digit number.) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('yyyy')

Result : “2021”

To get the current year (The year, from 00 to 99) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('yy')

Result : “21”

How to get the current hour in the Azure Data Factory (ADF)

To get the current hour (The hour, using a 12-hour clock from 1 to 12.) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('h')

Result : ” 9″

To get the current hour (The hour, using a 12-hour clock from 01 to 12.) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('hh')

Result : “09”

To get the current hour (The hour, using a 24-hour clock from 0 to 23.) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('H')

Result : “21”

To get the current hour (The hour, using a 24-hour clock from 00 to 23.) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('HH')

Result : “21”

How to get the current minute in the Azure Data Factory (ADF)

To get the current minute (The minute, from 0 through 59.) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('m')

Result : “0”

To get the current minute ( The minute, from 0 through 59 ) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('mm')

Result : “00”

How to get the current second in the Azure Data Factory (ADF)

To get the current second (The second, from 0 through 59.) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('s')

Result : “0”

To get the current second ( The second, from 0 through 59 ) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('ss')

Result : “00”

How to get the current time (AM/ PM) in the Azure Data Factory (ADF)

To get the current time designator (The first character of the AM/PM designator.) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('t')

Result : “P”

To get the current time designator ( The first character of the AM/PM designator. ) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('tt')

Result : “PM”

Convert String to DateTime in Azure Data Factory (ADF)

In many situation in the Azure Data factory, you get into a situation where you have the date time available but they are in the string format instead of the datetime data type. So you use following code to convert date time from string to timestamp data type.

Assume current date time is 1st September 2021 9 PM

utcnow()

Result : “2021-09-01T21:00:00.0000000Z”

You can also give format as well ‘D’ which will return the date with Day.

utcNow('D')

Result : “Wednesday, September 01, 2021”

Convert and Format DateTime in Azure Data Factory (ADF)

Whenever you wanted to convert the Datetime type in to specific format type you can use following code snippet.

Here you can convert the current Date time to ‘dd-MMM-yyyy’ format as string type.

Assume current date time is 1st September 2021 9 PM

formatDateTime(utcnow(), 'dd-MMM-yyyy'))

Result : “01-Sep-2021”

To get the current date in 01-01-2021 (dd-mm-yyyy format ) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('dd-MM-yyyy')

Result : “01-09-2021”

To get the current date in 12-31-2021 (mm-dd-yyyy format ) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('MM-dd-yyyy')

Result : “09-01-2021”

To get the current date in 12-31-21 (mm-dd-yy format ) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('MM-dd-yy')

Result : “09-01-21”

To get the current date in 21-12-31 (yy-mm-dd format ) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('MM-dd-yy')

Result : “09-01-21”

To get the current date time in 31-01-21 09:00 (dd-MM-yy hh:mm format ) in Azure data factory, you can use the following code expression:

Assume current date time is 1st September 2021 9 PM

utcnow('dd-MM-yy hh:mm')

Result : “01-09-21 09:00”

How to convert UTC time to local time in Azure Data Factory?

Using the convertTimeZone method you can convert the time to your local timezone.

For example: This will convert the UTC time to Alaska Time.

@convertTimeZone(utcnow(),'UTC','Alaska')

Date Time Conversion in Azure Data Factory Trick and Tips

Whenever you wanted to convert the date time in any specific format concept is very simple. You have use this table for conversion based on your need you can choose the format and put it inside the utcnow() function.

dd	The day of the month from 01 to 31
d	The day of the month from 1 to 31
MMM	The abbreviated name of the month like Sep, Aug
MM	Month between 01 to 12
yyyy	The year as a four-digit number
yy	The year as a two-digit number
hh	The hour using a 12-hour clock from 01 to 12.
HH	The hour using a 24-hour clock from 00 to 23.
mm	The minute between 00 to 59
ss	The seconds between 00 to 59

Example : utcnow( ‘dd-MM-yyyy hh:mm:ss) Just replace the character in this example using the table mentioned above to generate the date time or date only in your desired format.

You can also refer Microsoft Date Docs Here Link

Recommendations

Most of the Azure Data engineer finds it little difficult to understand the real world scenarios from the Azure Data engineer’s perspective and faces challenges in designing the complete Enterprise solution for it. Hence I would recommend you to go through these links to have some better understanding of the Azure Data factory.

Azure Data Factory Insights

Azure Data Engineer Real World scenarios

Azure Databricks Spark Tutorial for beginner to advance level

Latest Azure DevOps Interview Questions and Answers

You can also checkout and pinned this great Youtube channel for learning Azure Free by industry experts

IT Skills Upgrade – YouTube

Final Thoughts

By this, we have reached the last section of the article. In this article, we have learned how we can convert the date time in the azure data factory into multiple formats. You can refer to the table to format the date time as per the need. Hope you have found this article insightful and learned the new concept of date conversion in the azure data factory.

Please share your comments suggestions and feedbacks in the comment section below.