Date Functions

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

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

****************************

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] )

****************************

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

****************************

DATEPARSE:

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#

****************************

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

****************************

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