Date Functions - Tableau

This Document tells about Date Calculation, How to create calculations using Date Dimension.Tableau supports the following Date formats.Tableau Supported the following parts of the Date.


DATE_PART VALUES
'year'  Four-digit year

'quarter' 1-4

'month' 1-12 or "January", "February", and so on

'dayofyear' Day of the year; Jan 1 is 1, Feb 1 is 32, and so on


'day'  1-31

'weekday' 1-7 or "Sunday", "Monday", and so on

'week'  1-52

'hour'  0-23

'minute' 0-59

'second' 0-60


Usage of this Function: Dateadd:
We use this function to perform Date Calculations 
Like addition or subtraction at different levels.
Levels: Year, Quarter, Month,.........

EX: dateadd('month', 2, #12-Jan-2014#)
O/P: 12-Mar-2014

EX: 
Generally once Place the Order, we deliver after 2 Months, 
So i want to see Deliver Date for Each Order.

Steps:
1) Right click on [Order Date], click on "Create Calculated Field"
2) Define the Name, Syntax as like below.
Name: Deliver Date
Syntax: dateadd('month', 2,  [Order Date])
3) Then add the  [Order Date], [Deliver Date] to report.
4) Now report will show the Data at Year level, If you want to see in terms of Full date, Right click on [Order Date], click on "More" option then click on "Custom" then select "Month/Day/Year" format from the Drop down List.
*************
Usage of this Function: Datediff
We use this function to find the difference between two DATES in terms of specified date parts like Year, Month, Date and it gives the O/P in integers. 
Once created the Calculated field, drag that into Dimension window.

EX:DATEDIFF('day',[Order Date],[Ship Date] )

EX: 
I want to see How many days does take to deliver each Order ( Using Sample Database)

Steps:
1) Right click on [Order Date], click on "Create Calculated Field".

2) Define the Name, Syntax as like below.
Name: Days taken for Deliver
Syntax: DATEDIFF('day',[Order Date],[Ship Date] )

3) Then add the  [Order Date],  [Days taken for Deliver] to report.

4) Now you can find the [Order Date], [Days taken for Deliver] on the report . Days taken for Deliver shows Numerical Number.
***************************
Usage of this Function: Datename
We use this function to extract specific part of the Date, It gives O/P as string Date Type.

EX:

1) datename('year', #12-Jan-2014#)
O/P: 2014

2) datename('month', #12-Jan-2014#)
O/P: Jan

3) datename('weekday',  #12-Jan-2014#)
O/P: Sunday

**************
Usage of this Function: Datepart

We use this function to extract specific part of the Date,
 It gives O/P as Numerical Data.

EX:
1) datepart('year', #12-Jan-2014#)
O/P: 2014

2) datepart('month', #12-Jan-2014#)
O/P: 1

3) datepart('weekday',  #12-Jan-2014#)
O/P: 1

****
Converts a string to a datetime in the specified format. 
EX:
DATEPARSE ("dd.MMMM.yyyy", "15.April.2014") = #April 15, 2014# 
DATEPARSE ("h'h' m'm' s's'", "10h 5m 3s") = #10:05:03# 
***********
Usage of this Function: Datetrunc
We use this function to get the First Date of given Date at Defined Level. (Opening Date)

EX:
1) Datetrunc('year', #12- Feb-2014#)
O/P: 1-Jan-2014
2)  Datetrunc('month', #12- Feb-2014#)
O/P: 1-Feb-2014

EX: 
I want to see what is the Day of the Year for Each Transaction ( Days are 365 or 364)

1) Create a Calculated field like below to get the First Day of the Year for Each Order.
Name: Datetrunc - Year
Syntax: DATETRUNC('year',[Order Date])
2) Create another Calculated Field
Name: Day of the Year
Syntax: datediff('day', [Datetrunc - Year],[Order Date])
3) Drag the Fields [Order Date],  [Day of the Year], [Datetrunc - Year] onto Work Area.
***************
Usage of this Function: Day, Month, Year
-> We use these functions to extracts the Specific part from the Date, It means Year, Day, Month. And it gives Integer as Output. 

-> Once you created calculated field using any of these functions, that will be moved into Measures window since this function gives O/P in Number Data type.
EX:
1) day(#12-Jan-2014#)
O/P: 12

2) month(#12-Jan-2014#)
O/P:1

3) year(#12-Jan-2014#)
O/P:2014

5) Now you can find the [Order Date], [Deliver Date] on the report with "Month/Day/Year" format.