DATE_TRUNC (SQL)
Synopsis
DATE_TRUNC( datePart, dateExpression )
Description
-
DATE_TRUNC(datePart, date) returns a date expression that truncates the input date down to the specified datePart.
-
If date is of type %Library.PosixTimeOpens in a new tab (an encoded 64–bit signed integer), then DATE_TRUNC returns a timestamp of type %Library.PosixTimeOpens in a new tab.
-
If date is of any other type, then DATE_TRUNC returns a timestamp of type %Library.TimeStampOpens in a new tab in the format yyyy-mm-dd hh:mm:ss.fff.
The following statement truncates the date to the day.
SELECT DATE_TRUNC('month', CURRENT_DATE)
-
DATE_TRUNC is compatible with Sysbase and Microsoft SQL Server.
Arguments
datePart
The full or abbreviated name of a date or time part that the date is truncated down to. You can specify datePart in uppercase or lowercase. The supported date and time formats are enumerated in the following table:
Date Part | Abbreviations |
---|---|
year | yyyy, yy |
month | mm, m |
quarter | qq, q |
week | wk, ww |
weekday | dw, w |
day | dd, d |
dayofyear | dy, y |
hour | hh, h |
minute | mi, n |
second | ss, s |
millisecond | ms |
microsecond | mcs |
nanosecond | ns |
date
A date, time, or timestamp expression to be truncated. This expression may be one of the following types:
-
%Date logical value (+$H), also known as $HOROLOG format.
-
%PosixTime (%Library.PosixTimeOpens in a new tab) logical value (an encoded 64-bit signed integer).
-
%TimeStamp (%Library.TimeStampOpens in a new tab) logical value (YYYY-MM-DD HH:MM:SS.FFF), also known as ODBC format.
-
%String or string-compatible value, which can be in one of these formats:
$HOROLOG Date and Time FormatFormat 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 FormatFormat 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 FormatFormat 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.
-
The date argument has these restrictions and behaviors:
-
The date string must be complete and properly formatted with the appropriate number of elements and digits for each element and the appropriate separator character. Years must be specified as four digits. If you omit the date portion of an input value, DATE_TRUNC defaults to '1900-01-01'.
-
Date and time values must be within the valid range:
-
Years — 0001 through 9999
-
Months — 1 through 12
-
Days — 1 through 31
-
Hours — 00 through 23
-
Minutes — 0 through 59
-
Seconds — 0 through 59
The number of days in a month must match the month and year. For example, the date '02-29' is valid only if the specified year is a leap year.
-
-
In date values less than 10 (month and day), a leading zero in optional. Other non-canonical integer values are not permitted. For example, a Day value of '07' or '7' is valid, but '007', '7.0', or '7a' are not valid.
-
Time values are optional. If date specifies an incomplete time, zeros are supplied for the unspecified parts.
-
An hour value less than 10 must include a leading zero.
Examples
The following example performs the DATE_TRUNC function on a date written in $HOROLOG format. The result of this example is 2023–08–30 00:04:16.
SELECT DATE_TRUNC('ss','66716,256.467')
The following example performs the DATE_TRUNC function on a date written as a string. The result is 1980–01–01 00:00:00.
SELECT DATE_TRUNC('yy','11.25.80')
The following example performs the DATE_TRUNC function using CURRENT_DATE. The result varies, depending on the date, but the date is truncated to the day.
SELECT DATE_TRUNC('dd', CURRENT_DATE)
Alternatives
To call this function in ObjectScript code, use the DATE_TRUNC() method:
$SYSTEM.SQL.Functions.DATE_TRUNC(datePart, dateExpression)