DateTime conversions in Databricks SQL

Are you finding it difficult to operate on dates conversion in the Azure Databricks? 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 extract the year from the date in databricks SQL

To get the year from the date column in the Databricks SQL you can use the following code expression:

select year(date_column_name) from tableName;

Assume createdDate is ‘2008-09-11‘

select year(createdDate) from Order limit 1; 

Result : 2008

Get Crack Azure Data Engineer Interview Course

– 125+ Interview questions
– 8 hrs long Pre- recorded video course
– Basic Interview Questions with video explanation
– Tough Interview Questions with video explanation
– Scenario based real world Questions with video explanation
– Practical/Machine/Written Test Interview Q&A
– Azure Architect Level Interview Questions
– Cheat sheets
– Life time access
– Continuous New Question Additions

Here is the link to get Azure Data Engineer prep Course

How to extract the year from the String date in databricks SQL?

To get the year from the string column in the Databricks SQL you can use the following code expression:

select year(cast(date_column_name as Date)) from tableName;

String column should be in form: yyyy-mm-dd

Assume createdDate is ‘2009-09-11‘

Example would be:

select year(cast(createDateString as Date)) from Order limit 1; 

Result : 2009

How to extract the year from the datetime in databricks SQL?

To get the year from the timestamp column in the Databricks SQL you can use the following code expression:

select year(cast(timestamp_column_name as Date)) from tableName;

Assume createDateTimeStamp is ‘2005-08-01T00:00:00.000+0000‘

Example would be:

select year(cast(createDateTimeStamp as Date)) from Order limit 1; 

Result : 2005

How to extract the month from the date in databricks SQL

To get the month from the date column in the Databricks SQL you can use the following code expression:

select month(date_column_name) from tableName;

Assume createdDate is ‘2008-09-11‘

select month(createdDate) from Order limit 1; 

Result : 09

How to extract the month from the String date in databricks SQL?

To get the month from the string column in the Databricks SQL you can use the following code expression:

select month(cast(date_column_name as Date)) from tableName;

String column should be in form: yyyy-mm-dd

Assume createdDate is ‘2009-09-11‘

Example would be:

select year(cast(createDateString as Date)) from Order limit 1; 

Result : 09

How to extract the month from the datetime in databricks SQL?

To get the month from the timestamp column in the Databricks SQL you can use the following code expression:

select month(cast(timestamp_column_name as Date)) from tableName;

Assume createDateTimeStamp is ‘2005-08-01T00:00:00.000+0000‘

Example would be:

select month(cast(createDateTimeStamp as Date)) from Order limit 1; 

Result : 08

How to extract the day from the date in databricks SQL

To get the day from the date column in the Databricks SQL you can use the following code expression:

select day(date_column_name) from tableName;

Assume createdDate is ‘2008-09-11‘

select day(createdDate) from Order limit 1; 

Result : 11

How to extract the day from the String date in databricks SQL?

To get the day from the string column in the Databricks SQL you can use the following code expression:

select day(cast(date_column_name as Date)) from tableName;

String column should be in form: yyyy-mm-dd

Assume createdDate is ‘2009-09-11‘

Example would be:

select day(cast(createDateString as Date)) from Order limit 1; 

Result : 11

How to extract the day from the datetime in databricks SQL?

To get the day from the timestamp column in the Databricks SQL you can use the following code expression:

select day(cast(timestamp_column_name as Date)) from tableName;

Assume createDateTimeStamp is ‘2005-08-01T00:00:00.000+0000‘

Example would be:

select day(cast(createDateTimeStamp as Date)) from Order limit 1; 

Result : 01

How to extract day of week from date in databricks SQL

To get the day of week from the date column in the Databricks SQL you can use the following code expression:

select dayofweek(date_column_name) from tableName;

Assume createdDate is ‘2008-09-11‘

select dayofweek(createdDate) from Order limit 1; 

Result : 5

How to extract day of week from String date in databricks SQL?

To get the day of week from the string column in the Databricks SQL you can use the following code expression:

select dayofweek(cast(date_column_name as Date)) from tableName;

String column should be in form: yyyy-mm-dd

Assume createdDate is ‘2009-09-11‘

Example would be:

select dayofweek(cast(createDateString as Date)) from Order limit 1; 

Result : 5

How to extract day of week from datetime in databricks SQL?

To get the day of week from the timestamp column in the Databricks SQL you can use the following code expression:

select dayofweek(cast(timestamp_column_name as Date)) from tableName;

Assume createDateTimeStamp is ‘2005-08-01T00:00:00.000+0000‘

Example would be:

select dayofweek(cast(createDateTimeStamp as Date)) from Order limit 1; 

Result : 2

How to extract day of year from date in databricks SQL

To get the day of year from the date column in the Databricks SQL you can use the following code expression:

select dayofyear(date_column_name) from tableName;

Assume createdDate is ‘2008-09-11‘

select dayofyear(createdDate) from Order limit 1; 

Result : 255

How to extract day of year from String date in databricks SQL?

To get the day of year from the string column in the Databricks SQL you can use the following code expression:

select dayofyear(cast(date_column_name as Date)) from tableName;

String column should be in form: yyyy-mm-dd

Assume createdDate is ‘2009-09-11‘

Example would be:

select dayofyear(cast(createDateString as Date)) from Order limit 1; 

Result : 255

How to extract day of year from datetime in databricks SQL?

To get the day of year from the timestamp column in the Databricks SQL you can use the following code expression:

select dayofyear(cast(timestamp_column_name as Date)) from tableName;

Assume createDateTimeStamp is ‘2005-08-01T00:00:00.000+0000‘

Example would be:

select dayofweek(cast(createDateTimeStamp as Date)) from Order limit 1; 

Result : 213

How to extract hour from String date in databricks SQL?

To get the hour from the string column in the Databricks SQL you can use the following code expression:

select hour(cast(date_column_name as Timestamp)) from tableName;

String column should be in form: yyyy-mm-dd hh:MM:ss

Assume createdDate is ‘2009-09-11 13:08:05‘

Example would be:

select hour(cast(createDateString as Timestamp)) from Order limit 1; 

Result : 13

How to extract hour from datetime in databricks SQL?

To get the hour from the timestamp column in the Databricks SQL you can use the following code expression:

select hour(timestamp_column_name) from tableName;

Assume createDateTimeStamp is ‘2005-08-01 01:00:00‘

Example would be:

select hour(createDateTimeStamp) from Order limit 1; 

Result : 1

How to extract minute from String date in databricks SQL?

To get the minute from the string column in the Databricks SQL you can use the following code expression:

select minute(cast(date_column_name as TimeStamp)) from tableName;

String column should be in form: yyyy-mm-dd hh:MM:ss

Assume createdDate is ‘2009-09-11 13:08:05‘

Example would be:

select minute(cast(createDateString as Timestamp)) from Order limit 1; 

Result : 8

How to extract minute from datetime in databricks SQL?

To get the minute from the timestamp column in the Databricks SQL you can use the following code expression:

select minute(timestamp_column_name) from tableName;

Assume createDateTimeStamp is ‘2005-08-01 01:00:00‘

Example would be:

select minute(createDateTimeStamp) from Order limit 1; 

Result : 0

How to extract second from String date in databricks SQL?

To get the secondfrom the string column in the Databricks SQL you can use the following code expression:

select second(cast(date_column_name as TimeStamp)) from tableName;

String column should be in form: yyyy-mm-dd hh:MM:ss

Assume createdDate is ‘2009-09-11 13:08:05‘

Example would be:

select second(cast(createDateString as Timestamp)) from Order limit 1; 

Result : 8

How to extract Second from datetime in databricks SQL?

To get the second from the timestamp column in the Databricks SQL you can use the following code expression:

select second(timestamp_column_name) from tableName;

Assume createDateTimeStamp is ‘2005-08-01 01:30:00‘

Example would be:

select second(createDateTimeStamp) from Order limit 1; 

Result : 30

How to get current date in the databricks SQL?

To get the current date in the Databricks SQL you can use the following code expression:

current_date() or current_date

Assume current date is ‘2021-10-06‘

Example would be:

select current_date(); 

Result : 2021-10-06

How to get current timestamp in the databricks SQL?

To get the current timestamp in the Databricks SQL you can use the following code expression:

current_timestamp() or current_timestamp

Assume current date time is ‘2021-10-06T15:37:07.425+0000‘

Example would be:

select current_timestamp(); 

Result : 2021-10-06T15:37:07.425+0000

How to get current timezone in the databricks SQL?

To get the current timezone in the Databricks SQL you can use the following code expression it will return as Timezone:

current_timezone() or current_timezone

Assume current date time is ‘Etc/UTC‘

Example would be:

select current_timezone(); 

Result : Etc/UTC

How to convert from utc timezone to other timezone in the databricks SQL?

To get the timestamp in other than UTC timezone in the Databricks SQL, you can use the following code expression it will return as TimeStamp:

from_utc_timestamp(timestamp_in_utc, ‘desired_timezone’)

convert timestamp in to ‘Asia/Seoul’ :

select from_utc_timestamp('2021-10-06T15:37:07.425+0000', 'Asia/Seoul'); 

Result : 2021-10-07T00:37:07.425+0000

Assume current date time is ‘2021-10-06T15:37:07.425+0000 in Etc/UTC‘

Example to convert timestamp in to ‘Asia/Seoul’ :

select from_utc_timestamp('2021-10-06T15:37:07.425+0000', 'Asia/Seoul'); 

Result : 2021-10-07T00:37:07.425+0000

How to convert String to Date in databricks sql?

To convert the string to date in the Databricks SQL, you can use the following code expression, it will return as Date:

date(string_in_yyyy-mm-dd_format)

Assume current date string is ‘2021-10-07‘

Example to convert string to date:

select date('2021-10-07'); 

Result : 2021-10-07

How to convert String (in specific format )to Date in databricks sql?

To convert the string with specific format, to date in the Databricks SQL, you can use the following code expression, it will return as Date:

to_date(string, format)

Assume current date string is ‘10-07-2021‘

Example to convert string from dd-MM-yyyy format to Date:

select to_date('10-07-2021', 'dd-MM-yyyy'); 

Result : 2021-07-10

Example to convert string from MM-dd-yyyy format to Date:

select to_date('07-10-2021', 'MM-dd-yyyy'); 

Result : 2021-07-10

Example to convert string from MMM-dd-yyyy format to Date:

select to_date('Oct-07-2021', 'MMM-dd-yyyy'); 

Result : 2021-10-07

Example to convert string from MMM-dd-yy format to Date:

select to_date('Oct-07-21', 'MMM-dd-yy'); 

Result : 2021-10-07

Do refer the table in the bottom to get various available format

How to convert String (in specific format )to Timestamp in databricks sql?

To convert the string with specific format, to timestamp in the Databricks SQL, you can use the following code expression, it will return as Date:

to_timestamp(string, format)

Assume current timestamp string is ‘10-07-2021 13:25:35′

Example to convert string from dd-MM-yyyy HH:mm:ss format to Timestamp:

select to_timestamp('10-07-2021 13:25:35', 'dd-MM-yyyy HH:mm:ss'); 

Result : 2021-07-10T13:25:35.000+0000

Example to convert string from MM-dd-yyyy HH:mm:ss format to Timestamp:

select to_timestamp('07-10-2021 13:25:35', 'MM-dd-yyyy HH:mm:ss'); 

Result : 2021-07-10T13:25:35.000+0000

Example to convert string from custom MMM-dd-yyyy HH:mm:ss.SSS format to Date:

select to_timestamp('Oct-07-2021 13:25:35.811', 'MMM-dd-yyyy HH:mm:ss.SSS'); 

Result : 2021-10-07T13:25:35.811+0000

Example to convert string from MMM-dd-yy format to TimeStamp:

select to_timestamp('Oct-07-2021', 'MMM-dd-yyyy'); 

Result : 2021-10-07T00:00:00.000+0000

Do refer the table in the bottom to get various available format

How to convert from integer to timestamp in the databricks SQL?

To get the timestamp from the seconds in integer in the Databricks SQL, you can use the following code expression it will return as TimeStamp:

timestamp_seconds(integer)

select timestamp_seconds(1350219000); 

Result: 2012-10-14T12:50:00+0000

How to convert from decimal to timestamp in the databricks SQL?

To get the timestamp from the seconds in decimal in the Databricks SQL, you can use the following code expression it will return as TimeStamp:

timestamp_seconds(decimal)

select timestamp_seconds(1350219000.815); 

Result: 2012-10-14T12:50:00.815+0000

How to convert from integer to Date in the databricks SQL?

To get the date from the seconds in integer in the Databricks SQL, you can use the following code expression it will return as date:

to_date(timestamp_seconds(integer))

select to_date(timestamp_seconds(1350219000))

Result: 2012-10-14

How to convert from Decimal to Date in the databricks SQL?

To get the date from the seconds in decimal in the Databricks SQL, you can use the following code expression it will return as date:

to_date(timestamp_seconds(decimal))

select to_date(timestamp_seconds(1350219000.811))

Result: 2012-10-14

How to subtract dates in databricks SQL?

To subtract the dates in the Databricks the function is datediff() available in the Databricks SQL.

datediff(end_date,start_date)

For example you want to find the days between ‘2021-10-01’ to ‘2021-10-10’

select datediff('2021-10-10','2021-10-01')

Result: 9

How to get yesterday date in databricks SQL?

To get the yesterday date in the Databricks the function is da() available in the Databricks SQL.

date_sub(start_date,days)

For example you want to find the yesterday date then current date -1

select date_sub(current_date(),1);

Result: 2021-10-06

How to get Tomorrow date in databricks SQL?

To get the tomorrow date in the Databricks the function is da() available in the Databricks SQL.

date_sub(start_date,days)

For example you want to find the tomorrow date then current date, -1 used in function.

select date_sub(current_date(),-1);

Result: 2021-10-08

Date Time Conversion in Azure Databricks SQL 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 date time 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 : to_timestamp ( ‘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 Databricks Official Page 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 Databricks SQL 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 Databricks.

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