Date and Time Functions - Ocient Documentation

Basic Date and Time Functions

ADD_MONTHS

Adds the specified number of months to the date. Equivalent to using a date and adding a MONTH(value). Syntax

SQL

ADD_MONTHS(time, int)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function modifies by adding a specified number of months.
`int`	`INT`	The number of months to add to the `time` value.

Example

SQL

SELECT ADD_MONTHS('2022-11-15 04:18:00',2);

Output: 2023-01-15

CENTURY

Returns the number of centuries. Syntax

SQL

CENTURY(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes to return the number of its centuries.

Example In this example, the current date is 2023-01-03.

SQL

SELECT CENTURY(CURRENT_DATE());

Output: 21

CURDATE

Alias for CURRENT_DATE.

CURRENT_DATE

Alias for CURDATE. Returns the current date in the format YYYY-MM-DD. Syntax

SQL

CURRENT_DATE

Example

SQL

SELECT CURRENT_DATE;

Output: 2023-01-03

CURRENT_TIME

Returns the current time as a TIME value (e.g., hh:mm:ss.mm). Syntax

SQL

CURRENT_TIME

Example

SQL

SELECT CURRENT_TIME;

Output: 19:40:04

CURRENT_TIMESTAMP

Alias for NOW. Returns the current date and time as a TIMESTAMP value (e.g., YYYY-MM-DD hh:mm:ss.mmm). Syntax

SQL

CURRENT_TIMESTAMP

Example

SQL

SELECT CURRENT_TIMESTAMP;

Output: 2023-01-03 11:00:03.316674397

DATE_PART

Alias for EXTRACT.

DATE_TRUNC

Returns the date or timestamp entered, truncated to the specified precision. Syntax

SQL

DATE_TRUNC(precision, time)

Argument	Data Type	Description
`precision`	`CHAR`	The timestamp unit used to truncate the returned value. For the `precision` input, acceptable values are: `NANOSECOND` `MICROSECOND` `MILLISECOND` `SECOND` `MINUTE` `HOUR` `DAY` `WEEK` `MONTH` `QUARTER` `YEAR` `DECADE` `CENTURY` `MILLENNIUM`
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function truncates to the specified precision. The `time` input supports precision values DAY or larger.

Example This example uses CURRENT_TIMESTAMP set to 2023-01-03 11:00:59.114058182.

SQL

SELECT DATE_TRUNC('NANOSECOND', CURRENT_TIMESTAMP());

Output: 2023-01-03 11:01:05.280975906 Example This example uses CURRENT_TIMESTAMP set to 2023-01-01 11:00:59.114058181.

SQL

SELECT DATE_TRUNC('MONTH', CURRENT_TIMESTAMP());

Output: 2023-01-01 00:00:00.000000000 Example This example uses CURRENT_TIMESTAMP set to 2023-01-01 11:00:59.114058181.

SQL

SELECT DATE_TRUNC('CENTURY', CURRENT_TIMESTAMP());

Output: 2001-01-01 00:00:00.000000000

DAY

Alias for DAY_OF_MONTH.

DAY_OF_WEEK

Returns an integer, in the range of 1 to 7, that represents the day of the week. The value 1 is Sunday, and 7 is Saturday. Syntax

SQL

DAY_OF_WEEK(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes for its day of the week.

Example

SQL

SELECT DAY_OF_WEEK(CURRENT_DATE());

Output: 3

DAY_OF_YEAR

Alias for DOY. Returns an integer in the range 1 to 366 that represents the day of the year. Syntax

SQL

DAY_OF_YEAR(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes for its day of year.

Example This example uses CURRENT_TIMESTAMP set to 2023-01-03 11:00:59.114058182.

SQL

SELECT DAY_OF_YEAR(CURRENT_TIMESTAMP());

Output: 3

DAY_OF_MONTH

Alias for DAY. Extracts the day-of-month portion of a timestamp or date as an integer. Syntax

SQL

DAY_OF_MONTH(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes for its day of the month.

Example This example uses CURRENT_TIMESTAMP set to 2023-01-03 11:00:59.114058182.

SQL

SELECT DAY(CURRENT_TIMESTAMP());

Output: 3

DECADE

The decade is the year divided by 10. Syntax

SQL

DECADE(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes for its decade.

Example This example uses CURRENT_DATE set to 2023-01-03.

SQL

SELECT DECADE(CURRENT_DATE());

Output: 202

DOW

Alias for DAY_OF_WEEK.

DOY

Alias for DAY_OF_YEAR.

EOMONTH

Returns the last day of the month using the specified timestamp or date. If you specify both arguments, this function returns the last day of the month for the resulting timestamp or date after the system adds the specified number of months to the first argument value. Syntax

SQL

EOMONTH(time [, int])

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes for the last day of the month.
`int`	`INT`	Optional. An integer value that represents the number of months to add to the `time` value. If unspecified, the value defaults to 0.

Examples This example returns the last day of the month for the current timestamp.

SQL

SELECT EOMONTH(CURRENT_TIMESTAMP(),0);

Output: 2023-02-27 This example returns the last day of the month in November in 2022 by using the EOMONTH function to add one month to the last day of October in 2022.

SQL

SELECT EOMONTH(DATE('2022-10-31'), 1);

Output: 2022-11-30

EPOCH

The number of seconds after 1970-01-01 00:00:00 UTC. Syntax

SQL

EPOCH(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes for the number of seconds after 1970-01-01 00:00:00 UTC.

Example

SQL

SELECT EPOCH(CURRENT_TIMESTAMP());

Output: 1672743901

EXTRACT

Extract a component from a timestamp or date. Syntax

SQL

EXTRACT(precision FROM time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes for the component to be extracted.
`precision`	`CHAR`	The timestamp unit to be extracted. For the `precision` input, acceptable values are: `NANOSECOND` `MICROSECOND` `MILLISECOND` `SECOND` `MINUTE` `HOUR` `DAY` `WEEK` `MONTH` `QUARTER` `YEAR` `DECADE` `CENTURY` `MILLENNIUM`

Example This example uses CURRENT_TIMESTAMP set to 2023-01-03 11:00:59.114058182.

SQL

SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP());

Output: 11.0

HOUR

Extracts the hour portion of a timestamp as an integer. Syntax

SQL

HOUR(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes for its hour value.

Example This example uses CURRENT_TIMESTAMP set to 2023-01-03 11:00:59.114058182.

SQL

SELECT HOUR(CURRENT_TIMESTAMP());

Output: 11

ISODOW

Extracts the day of the week based on ISO 8601, which ranges from Monday (1) to Sunday (7). Syntax

SQL

ISODOW(timestamp or date)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes for its day-of-week value.

Example This example uses CURRENT_TIMESTAMP set to 2023-01-03 11:00:59.114058182.

SQL

SELECT ISODOW(CURRENT_TIMESTAMP());

Output: 2

ISDATE

Returns TRUE if the input argument can be successfully cast to a date, FALSE otherwise. Syntax

SQL

ISDATE(char)

Argument	Data Type	Description
`char`	`CHAR`	A string to cast as a `DATE`.

Example

SQL

SELECT ISDATE('2023-01-03');

Output: TRUE

MAKEDATETIME

Returns a timestamp consisting of the specified date and time. Syntax

SQL

MAKEDATETIME(date, time)

Argument	Data Type	Description
`date`	`CHAR`	A date string for timestamp creation.
`time`	`CHAR`	A time string for timestamp creation.

Example

SQL

SELECT MAKEDATETIME('2022-11-02','01:24:58');

Output: 2022-11-02 01:24:58.000000000

MILLISECOND

Extracts the millisecond portion of a timestamp as an integer. Syntax

SQL

MILLISECOND(timestring)

Argument	Data Type	Description
`timestring`	`TIMESTAMP`	A time value, specified as a timestamp or date, which the function extracts for the millisecond value.

Example This example uses CURRENT_TIMESTAMP set to 2023-02-22 13:59:25.286.

SQL

SELECT MILLISECOND(CURRENT_TIMESTAMP());

Output: 286

MINUTE

Extracts the minute portion of a timestamp or date as an integer. Syntax

SQL

MINUTE(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function extracts for the minute value.

Example This example uses CURRENT_TIMESTAMP set to 2023-02-22 14:00:40.764.

SQL

SELECT MINUTE(CURRENT_TIMESTAMP());

Output: 0

MONTH_NAME

Returns the calendar name in English of the month for the specified date. Syntax

SQL

MONTH_NAME(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes its month name.

Example This example uses CURRENT_TIMESTAMP set to 2023-02-22 13:59:25.286.

SQL

SELECT MONTH_NAME(CURRENT_TIMESTAMP());

Output: February

MONTHS_BETWEEN

Returns the difference between the two dates or timestamps in months as a DOUBLE. The fractional months component is based on a 31-day month. If the two dates have the same day or are both the last day of the month, this function returns a whole number instead. Syntax

SQL

MONTHS_BETWEEN(time1, time2)

Argument	Data Type	Description
`time1`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function compares to `time2` for the difference in months.
`time2`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function compares to `time1` for the difference in months.

Example

SQL

SELECT MONTHS_BETWEEN(CURRENT_TIMESTAMP(), ADD_MONTHS(CURRENT_TIMESTAMP(),11));

Output: -11

MONTH

Extracts the month portion of a timestamp or date as an integer. Syntax

SQL

MONTH(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes for its month value.

Example This example uses CURRENT_TIMESTAMP set to 2023-02-22 13:59:25.286.

SQL

SELECT MONTH(CURRENT_TIMESTAMP());

Output: 2

MSECS

The seconds field, including fractional parts. This is the same as EXTRACT(MILLISECONDS FROM time). Syntax

SQL

MSECS(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date. The function multiplies the seconds part of the value by 1,000.

Example

SQL

SELECT MSECS(CURRENT_TIMESTAMP());

Output: 37637.648

NANOS_TO_TIMESTAMP

Convert a number of nanoseconds into a timestamp equivalent to the duration after the epoch time, 1970-01-01 00:00:00 UTC. Syntax

SQL

NANOS_TO_TIMESTAMP(time)

Argument	Data Type	Description
`ns`	`INT`	A number of nanoseconds, specified as an integer, which the function converts into a timestamp equivalent to the duration after the epoch time.

Example

SQL

SELECT NANOS_TO_TIMESTAMP(10000000);

Output: 1969-12-31 16:00:00.010

NEXT_DAY

Returns the closest date after a specified date that lies on a specific day of the week. Syntax

SQL

NEXT_DAY(date, character)

Argument	Data Type	Description
`date`	`DATE`, `TIMESTAMP`, or `CHAR`	A time value, specified as a date, timestamp, or string, which the function uses to return the closest date for a specific day of the week. Can be any type that can be cast to `DATE`.
`character`	`CHAR`	A string representing a day of the week. The string must match the first three characters (case insensitive) of the English name of any day of the week. If this string does not match the prefix of any day, this function returns NULL.

Example

SQL

SELECT NEXT_DAY('2023-02-27', 'sat');

Output: 2023-03-04

NOW

Alias for CURRENT_TIMESTAMP.

QUARTER

Returns an integer between 1 and 4 representing the quarter of the year in which the specified date falls. Syntax

SQL

QUARTER(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function computes to return the quarter of the year.

Example

SQL

SELECT QUARTER(DATE('2023-02-08 09:00:00.000'));

Output: 1

ROUND

Returns the specified date or timestamp, rounded to the specified precision. The precision argument behaves like DATE_TRUNC. The function rounds up values to the specified precision. Syntax

SQL

ROUND(time, precision)

Argument	Data Type	Description
`precision`	`CHAR`	The timestamp unit used to round the returned value. For the `precision` input, acceptable values are: `NANOSECOND` `MICROSECOND` `MILLISECOND` `SECOND` `MINUTE` `HOUR` `DAY` `WEEK` `MONTH` `QUARTER` `YEAR` `DECADE` `CENTURY` `MILLENNIUM`
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function rounds to the specified precision.

Example

SQL

SELECT ROUND(CURRENT_TIMESTAMP() ,'HOUR');

Output: 2023-02-08 09:00:00.000

SECOND

Extracts the seconds portion of a timestamp as an integer. Syntax

SQL

SECOND(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `TIME`	A time value, specified as a timestamp or date, which the function uses to return the seconds value.

Example This example uses CURRENT_TIMESTAMP set to 2023-02-28 13:17:30.243.

SQL

SELECT SECOND(CURRENT_TIMESTAMP());

Output: 30

TIMESTAMP_TO_NANOS

Convert timestamp into nanoseconds after epoch as BIGINT. Syntax

SQL

TIMESTAMP_TO_NANOS(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function uses to return the nanoseconds after the epoch.

Example This example uses CURRENT_TIMESTAMP set to 2023-02-28 13:20:12.373.

SQL

SELECT TIMESTAMP_TO_NANOS(CURRENT_TIMESTAMP());

Output: 1677619212373708904

TO_TIMESTAMP

For information on using the TO_TIMESTAMP conversion function, see the TO_TIMESTAMP page.

USECS

Returns the seconds part of a time value, including fractional parts, as an integer. Same as EXTRACT(MICROSECONDS FROM time). Syntax

SQL

USECS(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date. The function multiplies the seconds part of the value by 1,000,000.

Example This example uses CURRENT_TIMESTAMP set to 2023-02-28 13:32:17.438.

SQL

SELECT USECS(CURRENT_TIMESTAMP());

Output: 17438041

WEEK

Returns the ISO-8601 week number, as an integer, of the specified timestamp or date value. The week starts on Monday, and the first week of a year contains January 4 of that year. For details, see the ISO week date definition. Syntax

SQL

WEEK(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function uses to return the week number.

Example This example uses CURRENT_TIMESTAMP set to 2023-02-28 13:37:37.128.

SQL

SELECT WEEK(CURRENT_TIMESTAMP());

Output: 9

YEAR

Extracts the year portion of a timestamp or date as an integer. Syntax

SQL

YEAR(time)

Argument	Data Type	Description
`time`	`TIMESTAMP` or `DATE`	A time value, specified as a timestamp or date, which the function uses to extract the year.

Example This example uses CURRENT_TIMESTAMP set to 2023-02-28 13:37:37.128.

SQL

SELECT YEAR(CURRENT_TIMESTAMP());

Output: 2023

Advanced Date and Time Functions

DATEADD

This function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value. The data type of the returned value for this function is dynamic. The return type depends on the argument supplied for date. If the value for date is a string literal date, DATEADD returns a datetime value. If another valid input data type is supplied for date, DATEADD returns the same data type. DATEADD raises an error if the string literal seconds scale exceeds three decimal place positions (.nnn) or if the string literal contains the time zone offset part. Syntax

SQL

DATEADD(datepart, number, date)

Argument	Data Type	Description
`datepart`	`CHAR`	The part of the date to which DATEADD adds an integer number. See the Datepart Values table for a list of supported arguments.
`number`	`INT`	An expression that can resolve to an `INT` that `DATEADD` adds to the specified `datepart` value of `date`. `DATEADD` accepts user-defined variable values for number. `DATEADD` truncates a specified `number` value that has a decimal fraction. In this situation, it does not round the number value.
`date`	`TIME`, `TIMESTAMP`, `DATE`	A column expression, expression, string literal, or user-defined variable. A string literal value must resolve to a date or timestamp. Use four-digit years to avoid ambiguity issues.

datepart	Abbreviations
`year`	yy, yyyy
`quarter`	qq, q
`month`	mm, m
`dayofyear`	dy, y
`day`	dd, d
`week`	wk, ww
`weekday`	dw, w
`hour`	hh
`minute`	mi, n
`second`	ss, s
`millisecond`	ms
`microsecond`	mcs
`nanosecond`	ns

DATEADD does not accept user-defined variable equivalents for the datepart arguments.

Examples This example shows each result from incrementing different datepart values by 1.

SQL

SELECT 'year', DATEADD(year,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'quarter', DATEADD(quarter,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'month', DATEADD(month,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'dayofyear', DATEADD(dayofyear,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'day', DATEADD(day,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'week', DATEADD(week,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'weekday', DATEADD(weekday,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'hour', DATEADD(hour,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'minute', DATEADD(minute,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'second', DATEADD(second,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'millisecond', DATEADD(millisecond,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'microsecond', DATEADD(microsecond,1, '2007-01-01 13:10:10.1111111')
    UNION ALL
SELECT 'nanosecond', DATEADD(nanosecond,1, '2007-01-01 13:10:10.1111111');

Output

Text

week                                         2007-01-08 13:10:10.111111100
millisecond                                  2007-01-01 13:10:10.112111100
second                                       2007-01-01 13:10:11.111111100
day                                          2007-01-02 13:10:10.111111100
minute                                       2007-01-01 13:11:10.111111100
dayofyear                                    2007-01-02 13:10:10.111111100
quarter                                      2007-04-01 13:10:10.111111100
month                                        2007-02-01 13:10:10.111111100
hour                                         2007-01-01 14:10:10.111111100
weekday                                      2007-01-02 13:10:10.111111100
year                                         2008-01-01 13:10:10.111111100
nanosecond                                   2007-01-01 13:10:10.111111101
microsecond                                  2007-01-01 13:10:10.111112100

Fetched 13 rows

In this example, each statement increments datepart by a number large enough to increment the next higher unit of date or time.

SQL

SELECT 'quarter', DATEADD(quarter,4,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'month', DATEADD(month,13,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'dayofyear', DATEADD(dayofyear,365,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'day', DATEADD(day,365,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'week', DATEADD(week,5,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'weekday', DATEADD(weekday,31,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'hour', DATEADD(hour,23,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'minute', DATEADD(minute,59,'2007-01-01 01:01:01.1111111')
	UNION ALL
SELECT 'second', DATEADD(second,59,'2007-01-01 01:01:01.1111111');

Output

Text

day                                          2008-01-01 01:01:01.111111100
weekday                                      2007-02-01 01:01:01.111111100
second                                       2007-01-01 01:02:00.111111100
month                                        2008-02-01 01:01:01.111111100
quarter                                      2008-01-01 01:01:01.111111100
dayofyear                                    2008-01-01 01:01:01.111111100
week                                         2007-02-05 01:01:01.111111100
hour                                         2007-01-02 00:01:01.111111100
minute                                       2007-01-01 02:00:01.111111100

Fetched 9 rows

DATEDIFF

This function returns an INT representing the difference between two date or time values, in a specified date or time unit. If only a time value is assigned to a date data type variable, DATEDIFF sets the value of the missing datepart to the default value: 1900-01-01. If only a date value is assigned to a variable of a time or date data type, DATEDIFF sets the value of the missing time part to the default value: 00:00:00. If either startdate or enddate have only a time part and the other only a datepart, DATEDIFF sets the missing time and datepart to the default values. If startdate and enddate have different date data types, and one has more time parts or fractional seconds precision than the other, DATEDIFF sets the missing parts of the other to 0. Syntax

SQL

DATEDIFF(datepart, startdate, enddate)

Argument	Data Type	Description
`datepart`	`CHAR`	The units in which the function returns the difference between the `startdate` and `enddate`. Commonly used `datepart` units include `month` or `second`. The `datepart` value cannot be specified in a variable, or in a quoted string like `'month'`. If `datepart` has a DATE value, and `startdate` and `enddate` are both assigned a TIME value, then the function returns `0`. See the Datepart Values table for a list of supported arguments.
`startdate`	`TIME`, `TIMESTAMP`, `DATE`	A starting date or time value to determine the difference from the `enddate` value. If only a time value is assigned, `DATEDIFF` sets the value of the missing `datepart` to the default value: `1900-01-01`
`enddate`	`TIME`, `TIMESTAMP`, `DATE`	An ending date or time value to determine the difference from the `startdate` value. If only a time value is assigned, `DATEDIFF` sets the value of the missing `datepart` to the default value: `1900-01-01`

datepart	Abbreviations
`year`	yy, yyyy
`quarter`	qq, q
`month`	mm, m
`dayofyear`	dy, y
`day`	dd, d
`week`	wk, ww
`weekday`	dw, w
`hour`	hh
`minute`	mi, n
`second`	ss, s
`millisecond`	ms
`microsecond`	mcs
`nanosecond`	ns

For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes, and 23.647 seconds.For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds.

Each specific datepart name and abbreviations for that datepart name return the same value.

Example The example shows how DATEDIFF calculates the difference between the two values based on the various datepart values.

SQL

SELECT 'year', DATEDIFF(year, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'quarter', DATEDIFF(quarter, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'month', DATEDIFF(month, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'dayofyear', DATEDIFF(dayofyear, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'day', DATEDIFF(day, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'week', DATEDIFF(week, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'hour', DATEDIFF(hour, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'minute', DATEDIFF(minute, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')
	UNION ALL
SELECT 'second', DATEDIFF(second, '2004-05-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Output

Text

day	        580
quarter	    7
year	    2
dayofyear	580
month	    20
hour	    13897
week	    83
minute      833761
second	    50025601

Time Zone Functions Formatting Functions

​Basic Date and Time Functions

​ADD_MONTHS

​CENTURY

​CURDATE

​CURRENT_DATE

​CURRENT_TIME

​CURRENT_TIMESTAMP

​DATE_PART

​DATE_TRUNC

​DAY

​DAY_OF_WEEK

​DAY_OF_YEAR

​DAY_OF_MONTH

​DECADE

​DOW

​DOY

​EOMONTH

​EPOCH

​EXTRACT

​HOUR

​ISODOW

​ISDATE

​MAKEDATETIME

​MILLISECOND

​MINUTE

​MONTH_NAME

​MONTHS_BETWEEN

​MONTH

​MSECS

​NANOS_TO_TIMESTAMP

​NEXT_DAY

​NOW

​QUARTER

​ROUND

​SECOND

​TIMESTAMP_TO_NANOS

​TO_TIMESTAMP

​USECS

​WEEK

​YEAR

​Advanced Date and Time Functions

​DATEADD

​DATEDIFF

​Related Links

Basic Date and Time Functions

ADD_MONTHS

CENTURY

CURDATE

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

DATE_PART

DATE_TRUNC

DAY

DAY_OF_WEEK

DAY_OF_YEAR

DAY_OF_MONTH

DECADE

DOW

DOY

EOMONTH

EPOCH

EXTRACT

HOUR

ISODOW

ISDATE

MAKEDATETIME

MILLISECOND

MINUTE

MONTH_NAME

MONTHS_BETWEEN

MONTH

MSECS

NANOS_TO_TIMESTAMP

NEXT_DAY

NOW

QUARTER

ROUND

SECOND

TIMESTAMP_TO_NANOS

TO_TIMESTAMP

USECS

WEEK

YEAR

Advanced Date and Time Functions

DATEADD

DATEDIFF

Related Links