Date/Time
Function Name | Description |
---|---|
CONVERT_TIMEZONE | Convert timestamp to the specified timezone. |
CURRENT_DATE | Returns the current date of the system. |
CURRENT_DATE_UTC | Returns the current date of the system based on the UTC timezone. |
CURRENT_TIME | Returns the current time for the system. |
CURRENT_TIMESTAMP | Returns the current timestamp for the system in UTC time only. |
DATEDIFF | Compares two dates or timestamps and returns the difference in days. |
DATETYPE | Constructs DATE using the values provided for year, month, and day parameters. |
DATE_ADD | Returns the sum of two expressions of time as another expression of time. |
DATE_DIFF | Returns the difference between two expressions of time as another expression of time. |
DATE_PART | Return subfields such as year or hour from date or timestamp values. |
DATE_SUB | Returns the difference of two expressions of time as another expression of time. |
DATE_TRUNC | Truncates the date or timestamp to the indicated precision. |
DAY | Returns the day of month of the date or timestamp. |
DAYOFMONTH | Returns the day of month of the date or timestamp. |
DAYOFWEEK | Returns the day of the week (from 1 to 7) of the date or timestamp. |
DAYOFYEAR | Returns the day of the year (from 1 to 366) of the date or timestamp. |
EXTRACT | Extracts the specified time unit from the specified date, time, or timestamp. |
HOUR | Extracts the hour number (from 0 to 23) for a given time or timestamp. |
LAST_DAY | Returns the last day of the month for the specified date or timestamp. |
MINUTE | Extracts the minute number (from 0 to 59) for a given time or timestamp. |
MONTH | Extracts the month number (from 1 to 12) for a given date or timestamp. |
MONTHS_BETWEEN | Returns the number of months between two date or timestamp values. |
NEXT_DAY | Returns the date or timestamp of the first specified day of week that occurs after the input date. |
OVERLAPS | Returns whether two intervals overlap. |
QUARTER | Extracts the quarter number (from 1 to 4) for a given date or timestamp. |
SECOND | Extracts the second number (from 0 to 59) for a given date or timestamp. |
TIMESTAMPADD | Add (or subtract) an interval of time from a date/timestamp value or column. |
TIMESTAMPDIFF | Return the amount of time between two date or timestamp values |
TIMESTAMPTYPE | Constructs a timestamp using the values provided for year, month, day, hour, minute, second, and millisecond parameters. |
TO_DATE | Converts the input expressions to the corresponding date. |
TO_TIME | Converts the input expressions to the corresponding time. |
TO_TIMESTAMP | Converts the input expressions to the corresponding timestamp. |
UNIX_TIMESTAMP | Returns the Unix epoch time representation of an ISO 8601 timestamp. |
WEEK | Extracts the week number (from 0 to 53) for a given date or timestamp. |
WEEKOFYEAR | Returns the week of year of the date or timestamp. |
YEAR | Extracts the year for a given date or timestamp. |
Date/Time Formatting
Format Element | Description | Example |
---|---|---|
AD/BC | Era indicator | AD, BC |
AMPM | Meridian indicator | AM, PM |
CC | Century indicator (0-99) | 19 |
WW | Week of year (0-52) | 4, 43 |
D | Day of week (1-7) | 6 |
DY | Abbreviated day name of week | Tue, Fri |
DAY | Full day name of week | Tuesday, Friday |
YYYY | Four digits of year | 1996 |
YY | Last two digits of year | 96 |
DDD | Day of year (1-366) | 5, 245 |
MM | Month (1-12) | 8 |
MON | Abbreviated month name | Mar, Oct |
MONTH | Full month name | March, October |
DD | Day of month (1-31) | 24 |
HH/HH12 | Hour of day (1-12) | 4 |
HH24 | Hour of day (0-23) | 21 |
MI | Minutes (0-59) | 22 |
SS | Seconds (0-59) | 54 |
FFF | Milliseconds | 121 |
TZD | Timezone abbreviation | UTC, PST |
TZO | Timezone offset | +02:00, -0800 |
Only the following characters are allowed in a format pattern: - / , . ; :
. If you need to let a value pass through to the output unmodified you can surround it with "
(for example "T"
).
If you convert a date to text, numeric values are zero padded for you. For example, MM
returns 04
for April.