Reference Guide > TDV Support for SQL Functions > TDV-Supported Convert Functions > TRUNC (for date/time)
 
TRUNC (for date/time)
The TRUNC function returns the integer portion of an expression, or, using the optional second argument, returns the expression with a specified number of decimal places. TRUNC does not take the sign of the expression into account (in other words, the decimal portion of both negative and positive expressions trend toward zero).
Syntax
TRUNC (first_arg, [format])
Remarks
• TRUNCATE works the same as TRUNC.
• The first argument is the keyword DATE or TIME or TIMESTAMP plus a quoted string containing the date or time expression to truncate.
• The data type and length of the result are the same as they are for the first argument.
• If the format argument is not present:
— TIMESTAMP truncates to day, with a time of 00:00:00.
— DATE or the date portion of a TIMESTAMP remains unchanged.
— TIME or the time portion of a TIMESTAMP is returned as 00:00:00.
• The optional second argument, format, is a STRING. Its values are listed in the table below. This argument is not case-sensitive.
Format Argument
TRUNC Output
CC
SCC
Truncates to the beginning year of the century. For example, 2050-01-01 truncates to 2001-01-01.
SYEAR, SYYYY
YEAR, YYYY, YYY, YY, Y
Truncates to the beginning of the current year.
IYYY, IYY, IY, I
Truncates to the beginning of the current ISO Year. An ISO year (ISO 8601 standard) starts on Monday of the week containing the first Thursday of January. It can start as early as 12/29 of the previous year, or as late as 01/04 of the current year.
Q
Truncates to the beginning of the current quarter.
MONTH, MON, MM, RM
Truncates to the beginning of the current month.
WW
Same day of the current week as the first day of the year.
IW
Same day of the current week as the first day of the ISO year (that is, Monday).
W
Same day of the current week as the first day of the month.
DDD, DD, J
Returns the date (with 00:00:00 for the hour portion of a TIMESTAMP).
DAY, DY, D
Returns the date of the starting day (Sunday) of the current week.
IDDD
ISO day of year, where day 1 of the year is Monday of the first ISO week. Range is 001-371.
ID
ISO day of the week, where Monday = 1 and Sunday = 7.
HH, HH12, HH24
Truncates to the hour, with 00 minutes and 00 seconds.
MI
Truncates to the minute, with 00 seconds.
Examples
The table gives examples of TRUNC (or its equivalent, TRUNCATE) with its available format definitions and the results.
SELECT Statement
Result
TRUNC (TIMESTAMP '1983-03-06 12:34:56', 'cc')
1901-01-01 00:00:00
TRUNC (TIMESTAMP '1983-03-06 15:59:31','Y')
1983-01-01 00:00:00
TRUNC (DATE '1983-03-06', 'yyyy')
1983-01-01
TRUNC (TIMESTAMP '2015-03-06 15:59:31','I')
2014-12-29 00:00:00
TRUNC (DATE '2015-03-06', 'i')
2014-12-29
TRUNC (TIMESTAMP '1983-03-06 15:59:31','q')
1983-01-01 00:00:00
TRUNC (DATE '1983-03-06', 'q')
1983-01-01
TRUNC (TIMESTAMP '1983-03-06 12:34:56', 'mm')
1983-03-01 00:00:00
TRUNC (DATE '1983-03-06', 'mm')
1983-03-01
TRUNC (DATE '2015-04-03', 'ww')
2015-04-02
TRUNC (DATE '2015-04-03', 'iw')
2015-03-30
TRUNC (DATE '2015-04-03', 'w')
2015-04-01
TRUNC (TIMESTAMP '2015-04-03 12:34:56', 'ddd')
2015-04-03 00:00:00
TRUNC (TIMESTAMP '2015-04-03 12:34:56', 'd')
2015-03-29 00:00:00
TRUNC (TIMESTAMP '2015-06-10 12:34:56', 'hh')
2015-06-10 12:00:00