In the previous article we looked into the first four date function. To day we will continue our discussion on the other date functions available in Tableau.
Many times we encounter a situation in which the dates in our data set are not in the date/datetime format that tableau recognizes. To convert this date field into a format that tableau recognizes is as a date/datetime we use the DATEPARSE function.
1- DATEPARSE:
Syntax: DATEPARSE(format,string)
The format parameter is a combination of symbols that represent the format in our dataset. The string parameter is the date or field in the data that we want to convert to a datetime type. Following are the most common date string values and the output when the DATEPARSE function is used on them:
2- DATETRUNC:
Syntax: DATETRUNC(date_part,date,[start_of_week])
If we look closely at the DATETRUNC function we observe that it is very similar to DATEPART. If the syntax is the same then where is the difference ? The difference lies in the fact that the result of DATEPART is a discrete data while the result of a DATETRUNC is a continuous dates.
3- DAY:
Syntax: DAY(date) : This function returns the day of the given date as integer. Following table illustrates the output with the following dates:
4- MONTH:
Syntax: MONTH(date) : Similar to the day function it returns the month of the specified date as integer.
5- YEAR
Syntax: YEAR(date) : This function returns the year of the specified date as integer.
6-NOW:
Syntax : NOW() : This function returns the current date and time depending on the nature of connection.
7- MAKEDATE
Syntax : MAKEDATE(‘year’,’month’,’day’)
This function helps us create a new date by specifying the year , month and day for the date. One thing that should be taken care of while using this function is that all the values : year , month and day must be numeric.
Example : MAKEDATE(2017,09,22) would create date 22-Sep-2017.
8- MAKETIME
Syntax: MAKETIME(hour,minute,second).
This function returns the time specified by hour, minute and second. Similar to the makedate function the values of hour , minute and second must be numeric. To see the range of value that these can take refer to the earlier post here.
I hope this post is informative and helps you in understanding time functions in tableau in a better way. Do let me know if I missed some.
References : Tableau online
