%String or string-compatible value, which can be in one of these formats:
$HOROLOG Date and Time Format
Format |
Example |
ddddd |
SELECT DATE_TRUNC('yy','66716') |
ddddd,sssss |
SELECT DATE_TRUNC('yy','66716,256') |
ddddd,sssss.fff |
SELECT DATE_TRUNC('yy','66716,256.467') |
where:
-
ddddd is the integer number of days since December 31, 1840.
-
sssss is the number of seconds since the start of that day.
-
fff is the integer number of fractional seconds. If you specify fractional seconds, the returned DATE_TRUNC value also includes fractional seconds.
Date Format
Format |
Example |
MM/DD/YYYY |
SELECT DATE_TRUNC('year','8/24/2022') |
MM-DD-YY |
SELECT DATE_TRUNC('year','12-31-99') |
MM-DD-YYYY |
SELECT DATE_TRUNC('year','8-24-2022') |
MM.DD.YY |
SELECT DATE_TRUNC('year','12.31.99') |
MM.DD.YYYY |
SELECT DATE_TRUNC('year','8.24.2022') |
Mmm DD YY |
SELECT DATE_TRUNC('year','Dec 30 92') |
Mmm DD YYYY |
SELECT DATE_TRUNC('year','January 23 2021') |
Mmm DD, YY |
SELECT DATE_TRUNC('year','Dec 30, 92') |
Mmm DD, YYYY |
SELECT DATE_TRUNC('year','January 23, 2021') |
MM/DD/YY |
SELECT DATE_TRUNC('year','12/31/99') |
where:
-
MM is the two-digit month.
-
DD is the two-digit number of days in the month.
-
Mmm is the spelled-out month. You can specify a minimum of three letters (for example, Mar) up to the full month name (for example, March).
-
YY and YYYY are the two-digit and four-digit forms of the year, respectively.
You can specify date as a combined date and time string. For example:
SELECT DATE_TRUNC('hh','12/22/2021 8:15:23')
If you specify a time without a date, DATE_TRUNC defaults to date 01/01/1900.
Time Format
Format |
Example |
HH: |
SELECT DATE_TRUNC('hour','10:') |
HH:MM |
SELECT DATE_TRUNC('mi','10:30') |
HH:MM:SS |
SELECT DATE_TRUNC('ss','10:30:59') |
HH...SS.FFF |
SELECT DATE_TRUNC('ms','10:30:59.245') |
HH...[AM|PM] |
SELECT DATE_TRUNC('mi','10:30PM') |
where:
-
HH is the two-digit number of hours into the day.
-
MM is the two-digit number of minutes into the hour.
-
SS is the two-digit number of seconds into the minute.
-
FFF is the number of fractional seconds.
You can specify date as a combined date and time string. For example:
SELECT DATEADD('hh',1,'12/22/2021 8:15:23')
If you specify a date without a time, DATE_TRUNC defaults to time 00:00:00.