Date Extraction Functions

These functions calculate or round-down timestamp values to the nearest specified time period, such as hour, day, week, month, and quarter.

You can use the calculated timestamps to aggregate data using a different grain. For example, you might apply the EXTRACTDAY() function to sales order dates to calculate a timestamp for midnight on the day that orders occur, so that you can aggregate the data by day.

Function Example Description Syntax

Extract Day
EXTRACTDAY("Order Date")
  • 2/22/1967 3:02:01 AM returns 2/22/1967 12:00:00 AM.
  • 9/2/2022 10:38:21 AM returns 9/2/2022 12:00:00 AM.

Returns a timestamp for midnight (12 AM) on the day in which the input value occurs. For example, if the input timestamp is for 3:02:01 AM on February 22nd, the function returns the timestamp for 12:00:00 AM on February 22nd.

EXTRACTDAY(expr)

Extract Hour
EXTRACTHOUR("Order Date")
  • 2/22/1967 3:02:01 AM returns 2/22/1967 3:00:00 AM.
  • 6/17/1999 11:18:30 PM returns 6/17/1999 11:00:00 PM.

Returns a timestamp for the start of the hour in which the input value occurs. For example, if the input timestamp is for 11:18:30 PM, the function returns the timestamp for 11:00:00 PM.

EXTRACTHOUR (expr)

Extract Hour of Day
EXTRACTHOUROFDAY("Order Date")
  • 2014/09/24 10:58:00 returns 2000/01/01 10:00:00.
  • 2014/08/13 11:10:00 returns 2000/01/01 11:00:00

Returns a timestamp where the hour equals the hour of the input value with default values for year, month, day, minutes, and seconds.

EXTRACTHOUROFDAY(expr)

Extract Millisecond
EXTRACTMILLISECOND("Order Date")
  • 1997/01/07 15:32:02.150 returns 1997/01/07 15:32:02.150.
  • 1997/01/07 18:42:01.265 returns 1997/01/07 18:42:01.265.
Returns a timestamp containing milliseconds for the input value. For example, if the input timestamp is for 15:32:02.150, the function returns the timestamp for 15:32:02.150.

EXTRACTMILLISECOND(expr)

Extract Minute
EXTRACTMINUTE("Order Date")
  • 6/17/1999 11:18:00 PM returns 6/17/1999 11:18:00 PM.
  • 9/2/2022 10:38:21 AM returns 9/2/2022 10:38:00 AM.

Returns a timestamp for the start of the minute in which the input value occurs. For example, if the input timestamp is for 11:38:21 AM, the function returns the timestamp for 11:38:00 AM.

EXTRACTMINUTE (expr)

Extract Month
EXTRACTMONTH("Order Date")
  • 2/22/1967 3:02:01 AM returns 2/1/1967 12:00:00 AM.
  • 6/17/1999 11:18:00 PM returns 6/1/1999 12:00:00 AM.

Returns a timestamp for the first day in the month in which the input value occurs. For example, if the input timestamp is for February 22nd, the function returns the timestamp for February 1st.

EXTRACTMONTH(expr)

Extract Quarter
EXTRACTQUARTER("Order Date")
  • 2/22/1967 3:02:01 AM returns 1/1/1967 12:00:00 AM, the first day of the first fiscal quarter.

  • 6/17/1999 11:18:00 PM returns 4/1/1999 12:00:00 AM, the first day of the second fiscal quarter.

  • 9/2/2022 10:38:21 AM returns 7/1/2022 12:00:00 AM, the first day of the third fiscal quarter.

    Tip: Use QUARTER (expr) to calculate just the ordinal quarter from the returned timestamp.

Returns a timestamp for the first day in the quarter in which the input value occurs. For example, if the input timestamp occurs in the third fiscal quarter, the function returns the timestamp for July 1st.

EXTRACTQUARTER(expr)

Extract Second
EXTRACTSECOND("Order Date")
  • 1997/01/07 15:32:02.150 returns 1997/01/07 15:32:02.
  • 1997/01/07 20:44:18.163 returns 1997/01/07 20:44:18.

Returns a timestamp for the input value. For example, if the input timestamp is for 15:32:02.150, the function returns the timestamp for 15:32:02.

EXTRACTSECOND(expr)

Extract Week
EXTRACTWEEK("Order Date")

  • 2014/09/24 10:58:00 returns 2014/09/21.

  • 2014/08/13 11:10:00 returns 2014/08/10.

Returns the date of the first day of the week (Sunday) in which the input value occurs. For example, if the input timestamp is for Wednesday, September 24th, the function returns the timestamp for Sunday, September 21st.

Note: If the first day of a week (i.e. Sunday) falls in a previous year and would therefore adversely affect the aggregation, the function returns the 7th day of the week (i.e. Saturday) in the current year instead of the first day of the week in the previous year. For example, 1/1/24, 1/2/24, and 1/3/24 all aggregate to Saturday 1/6/24, rather than Sunday 12/29/23.

EXTRACTWEEK(expr)

Extract Year
EXTRACTYEAR("Order Date")
  • 1967/02/22 03:02:01 returns 1967/01/01 00:00:00.
  • 1999/06/17 23:18:00 returns 1999/01/01 00:00:00.

Returns a timestamp for January 1st for the year in which the input value occurs. For example, if the input timestamp occurs in 1967, the function returns the timestamp for January 1st, 1967.

EXTRACTYEAR (expr)