Databricks – Formatting Dates with the date_format function

You will love this blog because date functions and formatting differ dramatically between databases, but I have covered that with brilliant examples and clear and concise explanations below.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/

Databricks uses the date_format command to transform raw date or timestamp data to format it as you want it presented.

For example, using date_format(current_date, ‘yyyy-MM-dd’) would format the current date as ‘YYYY-MM-DD’. You can tailor the output by including various format specifiers for years, months, days, hours, minutes, seconds, and more.

I will start with the basics, give you all the components, show you some top standards worldwide, and then show you 1,716 examples.

Below is a picture of two queries to see the syntax and how the date_format is implemented.

Here are a few basic components to format a Databricks date to get you a great start to understanding how to use the date_format command.

y: 4-digit year
yyyy: 4-digit year
yy: 2-digit year
MM: 2-digit month
M: 1 or 2-digit month
dd: 2-digit day of the month
d: 1 or 2-digit day of the month
MMMM: Full month name
MMM: Abbreviated month name
EEEE: Full day of the week
E: Abbreviated day of the week
D: Day of the year
q: Quarter of the year
G: Era (AD or BC)

Did you know you can use the date_format command to filter rows in the WHERE and the AND clauses on a Databricks system? The Databricks query example below uses the date_format function in the WHERE and AND clauses to only return rows where the customer hit the website where the day of the week is ‘Sat,’ the timestamp’s minutes = 54, and the month is December.

Below are the components to format dates and timestamps on a Databricks system.

current_date: Returns the current date in the default format (YYYY-MM-DD).

date_format(current_date, ‘yyyy’)
Returns the year component of the current date (four digits).

date_format(current_date, ‘yy’)
Returns the last two digits of the year component of the current date.

date_format(current_date, ‘M’)
Returns the month component of the current date (without leading zeros).

date_format(current_date, ‘MM’)
Returns the month component of the current date (with leading zeros).

date_format(current_date, ‘MMM’)
Returns the abbreviated month name (e.g., Jan, Feb).

date_format(current_date, ‘MMMM’)
Returns the full month’s name.

date_format(current_date, ‘d’)
Returns the day of the month (without leading zeros).

date_format(current_date, ‘dd’)
Returns the day of the month (with leading zeros).

date_format(current_date, ‘E’)
Returns the abbreviated day of the week (e.g., Mon, Tue).

date_format(current_date, ‘EEEE’)
Returns the full day of the week.

date_format(current_date, ‘h’)
Returns the hour component in 12-hour clock format (without leading zeros).

date_format(current_date, ‘hh’)
Returns the hour component in 12-hour clock format (with leading zeros).

date_format(current_date, ‘H’)
Returns the hour component in 24-hour clock format (without leading zeros).

date_format(current_date, ‘HH’)
Returns the hour component in 24-hour clock format (with leading zeros).

date_format(current_date, ‘m’)
Returns the minute component (without leading zeros).

date_format(current_date, ‘mm’)
Returns the minute component (with leading zeros).

date_format(current_date, ‘s’)
Returns the second component (without leading zeros).

date_format(current_date, ‘ss’)
Returns the second component (with leading zeros).

date_format(current_date, ‘a’)
Returns either ‘AM’ or ‘PM’ indicating the period of the day.

date_format(current_date, ‘S’)
Returns the fractional seconds without trailing zeros.

date_format(current_date, ‘SS’)
Returns the fractional seconds with one digit after the decimal point.

date_format(current_date, ‘SSS’)
Returns the fractional seconds with two digits after the decimal point.

date_format(current_date, ‘SSSS’)
Returns the fractional seconds with three digits after the decimal point.

date_format(current_date, ‘SSSSS’)
Returns the fractional seconds with four digits after the decimal point.

date_format(current_date, ‘SSSSSS’)
Returns the fractional seconds with six digits after the decimal point.

Here are the top 15 most common date format standards used around the world, along with explanations:

‘YYYY-MM-DD’ (ISO 8601): Example: 2024-01-13
ISO 8601 standard date format, widely adopted globally for its unambiguous representation.

‘DD/MM/YYYY’ (European): Example: 13/01/2024
Commonly used in European countries, featuring day, month, and year in the specified order.

‘MM/DD/YYYY’ (United States): Example: 01/13/2024
Standard date format in the United States, with month, day, and year in the specified order.

‘YYYY年MM月DD日’ (Japanese): Example: 2024年01月13日
Japanese date format using characters for year, month, and day.

‘DD.MM.YYYY’ (Russian): Example: 13.01.2024
Common in Russia, using dots as separators for day, month, and year.

‘YYYY/MM/DD’ (Chinese): Example: 2024/01/13
Standard date format in China, featuring year, month, and day in the specified order.

‘DD-MM-YYYY’ (India): Example: 13-01-2024
Common in India, using hyphens as separators for day, month, and year.

‘DD/MM/YYYY’ (Australian): Example: 13/01/2024
Similar to the European format, widely used in Australia.

‘YYYY년 MM월 DD일’ (South Korean): Example: 2024년 01월 13일
Korean date format using characters for year, month, and day.

‘YYYY-MM-DD’ (Global): Example: 2024-01-13
A concise global format uses hyphens as year, month, and day separators.

‘DD/MM/YYYY’ (Middle East): Example: 13/01/2024
Common in Middle Eastern countries, featuring day, month, and year.

‘MM/DD/YYYY’ (Canada): Example: 01/13/2024
Similar to the U.S. format, it is widely used in Canada.

‘YYYY.MM.DD’ (Global): Example: 2024.01.13
A concise global format using dots as separators for year, month, and day.

‘DD-MM-YYYY’ (South African): Example: 13-01-2024
Common in South Africa, hyphens are used as separators for day, month, and year.

‘YYYY/MM/DD’ (Taiwanese): Example: 2024/01/13
Taiwanese date format featuring year, month, and day in the specified order.


Here is a table with 1,716 examples of Databricks date_format examples.