Date / Time Functions
Adds the specified number of months to the date. Equivalent to using a date and adding a MONTH(value).
Syntax
Parameter | 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
Output: 2023-01-15
Returns the number of centuries.
Syntax
Parameter | 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.
Output: 21
Alias for CURRENT_DATE.
Alias for CURDATE.
Returns the current date in the format YYYY-MM-DD.
Syntax
Example
Output: 2023-01-03
Alias for NOW.
Returns the current date and time as a TIMESTAMP value (e.g., YYYY-MM-DD hh:mm:ss.mmm).
Syntax
Example
Output: 2023-01-03 11:00:03.316674397
Alias for EXTRACT.
Returns the date or timestamp entered, truncated to the specified precision.
Syntax
Parameter | 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.
Output: 2023-01-03 11:01:05.280975906
Example
This example uses CURRENT_TIMESTAMP set to 2023-01-01 11:00:59.114058181.
Output: 2023-01-01 00:00:00.000000000
Example
This example uses CURRENT_TIMESTAMP set to 2023-01-01 11:00:59.114058181.
Output: 2001-01-01 00:00:00.000000000
Alias for DAY_OF_MONTH.
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
Parameter | 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
Output: 3
Alias for DOY.
Returns an integer in the range 1 to 366 that represents the day of the year.
Syntax
Parameter | 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.
Output: 3
Alias for DAY.
Extracts the day-of-month portion of a timestamp or date as an integer.
Syntax
Parameter | 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.
Output: 3
The decade that is the year divided by 10.
Syntax
Parameter | 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.
Output: 202
Alias for DAY_OF_WEEK.
Alias for DAY_OF_YEAR.
Returns the last day of the timestamp or date.
Syntax
Parameter | 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 INT value that represents the number of months to add to the time value. If unspecified, the value defaults to 0. |
Example
Output: 2023-02-27
The number of seconds after 1970-01-01 00:00:00 UTC.
Syntax
Parameter | 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
Output: 1672743901
Extract a component from a timestamp or date.
Syntax
Parameter | 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.
Output: 11.0
Extracts the hour portion of a timestamp as an integer.
Syntax
Parameter | 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.
Output: 11
Extracts the day of week based on ISO 8601, which ranges from Monday (1) to Sunday (7).
Syntax
Parameter | 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.
Output: 2
Returns TRUE if the input argument can be successfully cast to a date, FALSE otherwise.
Syntax
Parameter | Data Type | Description |
---|---|---|
char | CHAR | A string to cast as a DATE . |
Example
Output: TRUE
Returns a timestamp consisting of the specified date and time.
Syntax
Parameter | Data Type | Description |
---|---|---|
date | CHAR | A date string for timestamp creation. |
time | CHAR | A time string for timestamp creation. |
Example
Output: 2022-11-02 01:24:58.000000000
Extracts the millisecond portion of a timestamp as an integer.
Syntax
Parameter | 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.
Output: 286
Extracts the minute portion of a timestamp or date as an integer.
Syntax
Parameter | 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.
Output: 0
Returns the calendar name in English of the month for the specified date.
Syntax
Parameter | 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.
Output: February
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
Parameter | 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
Output: -11
Extracts the month portion of a timestamp or date as an integer.
Syntax
Parameter | 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.
Output: 2
The seconds field, including fractional parts. This is the same as EXTRACT(MILLISECONDS FROM time).
Syntax
Parameter | 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
Output: 37637.648
Convert a number of nanoseconds into a timestamp equivalent to the duration after the epoch time, 1970-01-01 00:00:00 UTC.
Syntax
Parameter | 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
Output: 1969-12-31 16:00:00.010
Returns the closest date after a specified date that lies on a specific day of the week.
Syntax
Parameter | 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
Output: 2023-03-04
Alias for CURRENT_TIMESTAMP.
Returns an integer between 1 and 4 that represents the quarter of the year in which the specified date falls.
Syntax
Parameter | 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
Output: 1
Returns the specified date or timestamp, rounded to the specified precision. The precision parameter behaves like DATE_TRUNC. The function rounds up values to the specified precision.
Syntax
Parameter | 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
Output: 2023-02-08 09:00:00.000
Extracts the seconds portion of a timestamp as an integer.
Syntax
Parameter | 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.
Output: 30
Convert timestamp into nanoseconds after epoch as BIGINT.
Syntax
Parameter | 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.
Output: 1677619212373708904
For information on using the TO_TIMESTAMP conversion function, see the TO_TIMESTAMP page.
The seconds part of a time value, including fractional parts, returned as an integer. Same as EXTRACT(MICROSECONDS FROM time).
Syntax
Parameter | 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.
Output: 17438041
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
Parameter | 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.
Output: 9
Extracts the year portion of a timestamp or date as an integer.
Syntax
Parameter | 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.
Output: 2023
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 return value data type for this method 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
Parameter | 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.
Output
In this example, each statement increments datepart by a number large enough to additionally increment the next higher unit of date or time.
Output
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
Parameter | 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.
Output