SQL Reference

Date / Time Functions

Basic Date / 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)
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);
SELECT ADD_MONTHS('2022-11-15 04:18:00',2);
﻿
Output: 2023-01-15
CENTURY
Returns the number of centuries. 
Syntax
SQL
CENTURY(time)
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());
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
CURRENT_DATE
﻿
Example
SQL
SELECT CURRENT_DATE;
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
CURRENT_TIME
﻿
Example
SQL
SELECT CURRENT_TIME;
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
CURRENT_TIMESTAMP
﻿
Example
SQL
SELECT CURRENT_TIMESTAMP;
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)
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());
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());
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());
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)
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 week. 
Example
SQL
SELECT DAY_OF_WEEK(CURRENT_DATE());
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)
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());
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)
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 month.
Example
This example uses CURRENT_TIMESTAMP set to 2023-01-03 11:00:59.114058182. 
SQL
SELECT DAY(CURRENT_TIMESTAMP());
SELECT DAY(CURRENT_TIMESTAMP());
﻿
Output: 3
DECADE
The decade that is the year divided by 10.
Syntax
SQL
DECADE(time)
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());
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])
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);
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);
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)
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());
SELECT EPOCH(CURRENT_TIMESTAMP());
﻿
Output: 1672743901
EXTRACT
Extract a component from a timestamp or date.
Syntax
SQL
EXTRACT(precision FROM time)
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());
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP());
﻿
Output: 11.0
HOUR
Extracts the hour portion of a timestamp as an integer.
Syntax
SQL
HOUR(time)
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());
SELECT HOUR(CURRENT_TIMESTAMP());
﻿
Output: 11
ISODOW
Extracts the day of week based on ISO 8601, which ranges from Monday (1) to Sunday (7).
Syntax
SQL
ISODOW(timestamp or date)
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());
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)
ISDATE(char)
﻿
Argument
Data Type
Description
char
CHAR
A string to cast as a DATE .
Example
SQL
SELECT ISDATE('2023-01-03');
SELECT ISDATE('2023-01-03');
﻿
Output: TRUE
MAKEDATETIME
Returns a timestamp consisting of the specified date and time.
Syntax
SQL
MAKEDATETIME(date, time)
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');
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)
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());
SELECT MILLISECOND(CURRENT_TIMESTAMP());
﻿
Output: 286
MINUTE
Extracts the minute portion of a timestamp or date as an integer.
Syntax
SQL
MINUTE(time)
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());
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)
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());
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)
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));
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)
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());
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)
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());
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)
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);
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)
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');
SELECT NEXT_DAY('2023-02-27', 'sat');
﻿
Output: 2023-03-04
NOW
Alias for CURRENT_TIMESTAMP﻿.
QUARTER
Returns an integer between 1 and 4 that represents the quarter of the year in which the specified date falls.
Syntax
SQL
QUARTER(time)
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'));
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)
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');
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)
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()); 
SELECT SECOND(CURRENT_TIMESTAMP()); 
﻿
Output: 30
TIMESTAMP_TO_NANOS
Convert timestamp into nanoseconds after epoch as BIGINT.
Syntax
SQL
TIMESTAMP_TO_NANOS(time)
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 epoch.
Example
This example uses CURRENT_TIMESTAMP set to 2023-02-28 13:20:12.373. 
SQL
SELECT TIMESTAMP_TO_NANOS(CURRENT_TIMESTAMP());
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
The seconds part of a time value, including fractional parts, returned as an integer. Same as EXTRACT(MICROSECONDS FROM time).
Syntax
SQL
USECS(time)
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());
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 more information, see the ISO week date definition. 
Syntax
SQL
WEEK(time)
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());
SELECT WEEK(CURRENT_TIMESTAMP());
﻿
Output: 9
YEAR
Extracts the year portion of a timestamp or date as an integer.
Syntax
SQL
YEAR(time)
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());
SELECT YEAR(CURRENT_TIMESTAMP());
﻿
Output: 2023
Advanced Date / 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 return 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)
DATEADD (datepart, number, date)
﻿
Argument
Data Type
Description
datepart
CHAR
The part of 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.
﻿
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');
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
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 additionally 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'); 
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
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)
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
﻿
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');
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
day	        580
quarter	    7
year	    2
dayofyear	580
month	    20
hour	    13897
week	    83
minute      833761
second	    50025601
﻿
Related Links
﻿Time Zone Functions﻿﻿
﻿Formatting Functions﻿﻿

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.

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.

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.

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.

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.

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

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

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.

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.

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

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.

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.

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

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

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

Argument	Data Type	Description
datepart	CHAR	The part of 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.

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

Updated 16 May 2024

Did this page help you?

Data Types

Formatting Functions