For this guide, I want you to refer to two things to make it easy for you to understand the different date functions in Tableau. 1) I will be using Tableau's sample data's "Order Date" field from Superstore Sales to refer to times we want to make a calculation. 2) As I start writing this blog, it is 03/02/2025 6.53pm- this will come in helpful for understanding some calculations too.
Today and Now
TODAY:
Example: TODAY()
Returns the date that is in real time. Using when I'm writing this blog, this function would return 03/02/2025. You don't need to insert any date field in the brackets.
NOW:
Example: NOW()
Returns the date and time that is in real time. Using when I'm writing this blog, this function would return 03/02/2025 18:53:00. You don't need to insert any date field in the brackets.
Year, Quarter, Month, Week, and Day
Year, Quarter, Month, Week, and Day all return values in integers.
YEAR:
Example: YEAR(Order Date)
Returns only the year of Order Date as a integer- e.g., 2025
QUARTER:
Example: QUARTER(Order Date)
Returns only the quarter number of Order Date as an integer- e.g., 1 (being the first quarter)
MONTH:
Example: MONTH(Order Date)
Returns only the month number of Order Date as an integer- e.g., 2 (being February)
WEEK:
Example: WEEK(Order Date)
Returns only the week number of Order Date as an integer- e.g., 6 (being the 6th week of 2025)
DAY:
Example: DAY(Order Date)
Returns only the day number of the month of Order Date as an integer- e.g., 3 (being the 3rd day of February)
DatePart
DATEPART is a comprehensive extension to the functions above. On top of YEAR, QUARTER, MONTH, WEEK, and DAY, this function allows you the access the following as an integer value:
- weekday: returns the weekday of the month as an integer: 1-7 (Monday-Sunday)
- hour: returns the hour of the day as an integer: 0-24 hours
- minute: returns the minute of the hour as an integer: 0-60 minutes
- second: returns the second of the minute as integer: 0-60 second
- dayofyear: returns the day of year as an integer: 1-365 days
Example: DATEPART('month', Order Date).
The 'month' or any other date parts like year, hour, dayofyear, etc., must be written in lower case letters.
DateName
All the date parts mentioned in the DatePart section can also be used in the DATENAME function.
What's the difference between DATEPART and DATENAME?
- Values are given as a string.
- Only month and weekday actually change in terms of the value that's displayed after running the calculation. E.g., you will get February as opposed to 2 or you will get Monday as opposed 1., The rest will be numbers but still in a string format.
Example: DATENAME('month', Order Date) can give us January-December.
DateAdd
All the date parts mentioned in the DatePart section can also be used in the DATEADD function. This functions allows you to add (or subtract) the specified date part from Order Date. Values are returned as a date as opposed to an integer or string.
Example: DATEADD('year', 1, Order Date).
If we take 03/02/2025, the example above will convert give us an answer of 03/02/2026.
DateDiff
This function allows you to find the difference between two dates. You have to specify the date part you want to subtract, all the date parts in the DatePart section can be used, and the answer is returned as an integer.
Example: DATEDIFF('month', 1, Order Date, Ship Date)
If we take 03/02/2025 as the Order Date and 03/03/2025 as Ship Date, the example above will give us an answer of 1.
DateTrunc
This functions allows you to truncate a date to a specified level (specified by the date part). The answers are given as a date, truncated to the start of the specified date part. All the date parts in the DatePart section can be used here.
Example: DATETRUNC('Month', Order Date)
If we take 03/02/2025 as the Order Date, the answer will be given as February (in a date format, as opposed to a string or integer).
DateParse
Allows you to convert a string to a date format. This function is powerful in the sense that it allows you to convert a string in many different date formats.
Example: DATEPARSE ('yyyy-mm-dd', Order Date)
If we take the assumption that Order Date in this instance is a string and it has the value of Feb, 3 2025, the example above gives us an answer of 2025-February-03.
Below is a snapshot of the different formats that can be picked in DATEPARSE:
