Data Functions Flashcards
DATE()
DATE(expression)
Returns date
Returns a date given a number, string, or date <expression>.</expression>
If DATE() does not recognize the input as a date, what should you use instead?
DATEPARSE()
3 functions that changes data from string/integer/date into a date
(doesn’t change the date in any way like truncating or finding the difference)
DATE(expression)
DATE(“September 22, 2018”)
DATE(#2018-09-22 14:52#)
Remember: this returns just date and no time
DATEPARSE(date format, date string)
DATEPARSE(‘yyyy-MM-dd’, “2018-09-22”)
MAKEDATE(year, month, day)
MAKEDATE(2018,9,22)
required integer
DATEADD()
DATEADD(date_part, interval, date)
Returns date
Returns the <date> with the specified number <interval> added to the specified <date_part> of that date. For example, adding three months or 12 days to a starting date.</date_part></interval></date>
DATEADD(‘week’, 1, [due date])
Push out all due dates by one week
Results in date
Add 280 days to the date February 20, 2021
DATEADD(‘day’, 280, #2/20/21#)
Result: #November 27, 2021#
DATEDIFF()
DATEDIFF(date_part, date1, date2, [start_of_week])
Returns integer
Returns the number of date parts (weeks, years, etc) between two dates in units of <date_part></date_part>
Number of days between March 25, 1986 and February 20, 2021
DATEDIFF(‘day’, #3/25/1986#, #2/20/2021#)
Result: 12,751
Number of days between March 25, 1986 and February 20, 2021 (start of week is Sunday)
DATEDIFF(‘day’, #3/25/1986#, #2/20/2021#, ‘sunday’)
How many months someone was in a band?
Using [date left band] & [date joined band]
DATEDIFF(‘month’, [date joined band], [date left band])
DATENAME()
DATENAME(date_part, date, [start_of_week])
Returns the name of the specified date part as a discrete string.
Bonus: An inverse function is DATEPARSE, which takes a string value and formats it as a date.
DATENAME(‘year’, #3/25/1986#)
Result: “1986”
DATENAME(‘month’, #1986-03-25#)
Result: “March”
DATEPARSE()
DATEPARSE(date_format, date_string)
Returns specifically formatted strings as dates.
The <date_format> argument describes how the <date_string> field is arranged. Because of the variety of ways the string field can be ordered, the <date_format> must match exactly.</date_format></date_string></date_format>
Bonus: Inverse functions, which take dates apart and return the value of their parts, are DATEPART (integer output) and DATENAME (string output).
DATEPARSE(‘yyyy-MM-dd’, “1986-03-25”)
March 25, 1986
DATEPART()
DATEPART(date_part, date, [start_of_week])
Returns the name of the specified date part as an integer.
Bonus: An inverse function is DATEPARSE, which takes a string value and formats it as a date
DATEPART(‘year’, #1986-03-25#)
1986
DATEPART(‘month’, #1986-03-25#)
3
DATETRUNC()
DATETRUNC(date_part, date, [start_of_week])
Think “date rounding”
Output: Date
Changes the date, not the date format (i.e. doesn’t stop showing time)
Truncates the <date> to the accuracy specified by the <date_part>. This function returns a new date. (I.e. when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month.)</date_part></date>
Bc every date must have a value for day, month, quarter, and year, DATETRUNC sets the values as the lowest value for each date part up to the date part specified.
DATETRUNC(‘day’, #5/17/2022 3:12:48 PM#)
5/17/2022 12:00:00 AM
DATETRUNC(‘day’, #9/22/2018#)
9/22/2018
DATETRUNC(‘iso-week’, #9/22/2018#)
9/17/2018
(the monday of the week containing 9/22/2018)
DATETRUNC(quarter, #9/22/2018#)
7/1/2018
(the first day of the quarter containing 9/22/2018)
DAY()
DAY(date)
Returns the day of the month (1-31) as an integer.
Day(#September 22, 2018#)
22
ISDATE()
ISDATE(string)
Checks if the string is a valid date format.
Returns true if a given <string> is a valid date.</string>
Returns True or False
ISDATE(09/22/2018)
True
ISDATE(22SEP18)
false
ISOQUARTER()
ISOQUARTER(date)
Returns Integer
Returns the ISO8601 week-based quarter of a given <date> as an integer.</date>
ISO = internationally recognized date and calendar format
ISO example: September 27, 2022 at 6 p.m. is represented as 2022-09-27 18:00:00.000 (Note that ISOQUARTER would be 3)
(In this example, the input is a date value and it doesn’t have to be in ISO format. The ISO just will convert it to ISO format and then do the calculation.)
MAKEDATE()
MAKEDATE(year, month, day)
Returns a date value constructed from the specified <year>, <month>, and <day>.</day></month></year>
Requires numerical inputs
MAKEDATE(1986,3,25)
1986-03-25
Turn a string date into a date
Try the DATE function.
If DATE does not recognize the input try using DATEPARSE.
You tried to turn a sting date into an integer date with the DATE function.
Issue: DATE does not recognize the input
What other function can you use?
try using DATEPARSE
MAKEDATETIME()
MAKEDATETIME(date, time)
Returns a datetime that combines a <date> and a <time>.</time></date>
The date can be a date, datetime, or a string type. The time must be a datetime.
BONUS:
Same output as MAKETIME
only available for MYSQL-compatible connections
MAKEDATETIME(#1899-12-30#, #07:59:00#)
#12/30/1899 7:59:00 AM#
MAKETIME()
MAKETIME(hour, minute, second)
Returns a date value constructed from the specified <hour>, <minute>, and <second>.</second></minute></hour>
date is 1/1/1899
MAKETIME(14, 52, 40)
1/1/1899 14:52:40
Because Tableau does not support a time data type, only datetime, the output is a datetime. The date portion of the field will be 1/1/1899.
BONUS:
Similar to MAKEDATETIME
MONTH()
Integer
Returns the month of the given <date> as an integer.</date>
NOW()
Datetime
Returns the current local system date and time.
NOW() = 1986-03-25 1:08:21 PM
date_part
a string constant that tells the function what part of a date to consider, such as day, week, quarter, etc.
Examples:
‘year’
‘dayofyear’ (Jan 1)
‘minute’
‘iso-week’
What day starts iso-week?
What is the range of outputs?
Monday
1-52
What day starts iso-weekday?
What is the range of outputs?
Monday
1-7
Date format is
‘March 25, 1986’
What is the data type and result?
String ‘March 25, 1986’
Date format is
#3/25/1986#
What is the data type and result?
Date #3/25/1986#
Date format is
03/25/1986
What is the data type and result?
Integer
0.0000604230
Date format is
1986-03-25
What is the data type and result?
Integer 1,958
How can you change the default start of the week to Sunday?
Right click data source > Date properties > Change “Week start:” to Sunday
