Date Functions

Question 1

DATE()

Answer 1

DATE(expression)

Returns date

Returns a date given a number, string, or date <expression>.</expression>

Question 2

If DATE() does not recognize the input as a date, what should you use instead?

Answer 2

DATEPARSE()

Question 3

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)

Answer 3

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

Question 4

DATEADD()

Answer 4

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>

Question 5

DATEADD(‘week’, 1, [due date])

Answer 5

Push out all due dates by one week

Results in date

Question 6

Add 280 days to the date February 20, 2021

Answer 6

DATEADD(‘day’, 280, #2/20/21#)

Result: #November 27, 2021#

Question 7

DATEDIFF()

Answer 7

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>

Question 8

Number of days between March 25, 1986 and February 20, 2021

Answer 8

DATEDIFF(‘day’, #3/25/1986#, #2/20/2021#)

Result: 12,751

Question 9

Number of days between March 25, 1986 and February 20, 2021 (start of week is Sunday)

Answer 9

DATEDIFF(‘day’, #3/25/1986#, #2/20/2021#, ‘sunday’)

Question 10

How many months someone was in a band?

Using [date left band] & [date joined band]

Answer 10

DATEDIFF(‘month’, [date joined band], [date left band])

Question 11

DATENAME()

Answer 11

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.

Question 12

DATENAME(‘year’, #3/25/1986#)

Answer 12

Result: “1986”

Question 13

DATENAME(‘month’, #1986-03-25#)

Answer 13

Result: “March”

Question 14

DATEPARSE()

Answer 14

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

Question 15

DATEPARSE(‘yyyy-MM-dd’, “1986-03-25”)

Answer 15

March 25, 1986

Question 16

DATEPART()

Answer 16

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

Question 17

DATEPART(‘year’, #1986-03-25#)

Answer 17

1986

Question 18

DATEPART(‘month’, #1986-03-25#)

Answer 18

3

Question 19

DATETRUNC()

Answer 19

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.

Question 20

DATETRUNC(‘day’, #5/17/2022 3:12:48 PM#)

Answer 20

5/17/2022 12:00:00 AM

Question 21

DATETRUNC(‘day’, #9/22/2018#)

Answer 21

9/22/2018

Question 22

DATETRUNC(‘iso-week’, #9/22/2018#)

Answer 22

9/17/2018

(the monday of the week containing 9/22/2018)

Question 23

DATETRUNC(quarter, #9/22/2018#)

Answer 23

7/1/2018

(the first day of the quarter containing 9/22/2018)

Question 24

DAY()

Answer 24

DAY(date)
Returns the day of the month (1-31) as an integer.

Question 25

Day(#September 22, 2018#)

Answer 25

22

Question 26

ISDATE()

Answer 26

ISDATE(string) Checks if the string is a valid date format. Returns true if a given is a valid date. Returns True or False

Question 27

ISDATE(09/22/2018)

Answer 27

True

Question 28

ISDATE(22SEP18)

Answer 28

false

Question 29

ISOQUARTER()

Answer 29

ISOQUARTER(date) Returns Integer Returns the ISO8601 week-based quarter of a given as an integer. 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.)

Question 30

MAKEDATE()

Answer 30

MAKEDATE(year, month, day) Returns a date value constructed from the specified , , and . Requires numerical inputs

Question 31

MAKEDATE(1986,3,25)

Answer 31

#1986-03-25#

Question 32

Turn a string date into a date

Answer 32

Try the DATE function. If DATE does not recognize the input try using DATEPARSE.

Question 33

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?

Answer 33

try using DATEPARSE

Question 34

MAKEDATETIME()

Answer 34

MAKEDATETIME(date, time) Returns a datetime that combines a and a . 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

Question 35

MAKEDATETIME(#1899-12-30#, #07:59:00#)

Answer 35

**#**12/30/1899 7:59:00 AM**#**

Question 36

MAKETIME()

Answer 36

MAKETIME(hour, minute, second) Returns a date value constructed from the specified , , and . date is 1/1/1899

Question 37

MAKETIME(14, 52, 40)

Answer 37

#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

Question 38

MONTH()

Answer 38

Integer Returns the month of the given as an integer.

Question 39

NOW()

Answer 39

Datetime Returns the current local system date and time. NOW() = 1986-03-25 1:08:21 PM

Question 40

date_part

Answer 40

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'

Question 41

What day starts iso-week? What is the range of outputs?

Answer 41

Monday 1-52

Question 42

What day starts iso-weekday? What is the range of outputs?

Answer 42

Monday 1-7

Question 43

Date format is 'March 25, 1986' What is the data type and result?

Answer 43

String 'March 25, 1986'

Question 44

Date format is #3/25/1986# What is the data type and result?

Answer 44

Date #3/25/1986#

Question 45

Date format is 03/25/1986 What is the data type and result?

Answer 45

Integer 0.0000604230

Question 46

Date format is 1986-03-25 What is the data type and result?

Answer 46

Integer 1,958

Question 47

How can you change the default start of the week to Sunday?

Answer 47

Right click data source > Date properties > Change "Week start:" to Sunday