DATE_TRUNC (SQL)

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

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

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.