TRUNC_TIMESTAMP scalar function

The TRUNC_TIMESTAMP function returns a TIMESTAMP WITHOUT TIME ZONE value that is the expression, truncated to the unit that is specified by the format-string. If format-string is not specified, expression is truncated to the nearest day, as if 'DD' was specified for format-string.

Read syntax diagramSkip visual syntax diagramTRUNC_TIMESTAMP(expression 'DD',format-string )

The schema is SYSIBM.

expression
An expression that returns a value of any of the following built-in data types: a timestamp, a character string, or a graphic string. If expression is a character or graphic string, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a timestamp with an actual length that is not greater than 255 bytes. A time zone in a string representation of a timestamp is ignored. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.
format-string
An expression that returns a built-in character string or graphic string data type, with a length that is not greater than 255 bytes. format-string contains a template of how the timestamp represented by expression should be truncated. For example, if format-string is 'DD', the timestamp that is represented by expression is truncated to the nearest day. format-string must be a valid template for a timestamp, and not include leading or trailing blanks. Allowable values for format-string are listed in the following table.
Table 1. ROUND_TIMESTAMP and TRUNC_TIMESTAMP format models
Format model Description of rounding or truncating for the format model ROUND_TIMESTAMP example TRUNC_TIMESTAMP example
CC
SCC
Century.

Start of changeOne greater than the first two digits of a four digit year. End of change

Start of changeFor ROUND_TIMESTAMP, rounds up on the 50th year of the century.End of change

Input Value:
1897-12-04-12.22.22.000000
Result:
1901-01-01-00.00.00.000000
Input Value:
1897-12-04-12.22.22.000000
Result:
1801-01-01-00.00.00.000000
SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y
Year.

Start of changeFor ROUND_TIMESTAMP, rounds up on July 1 to January 1st of the next year.End of change

Input Value:
1897-12-04-12.22.22.000000
Result:
1898-01-01-00.00.00.000000
Input Value:
1897-12-04-12.22.22.000000
Result:
1897-01-01-00.00.00.000000
IYYY
IYY
IY
I
ISO year.

Start of changeFor ROUND_TIMESTAMP, rounds up on July 1 to the first day of the next ISO year. The first day of the ISO year is defined as the Monday of the first ISO week.End of change

Input Value:
1897-12-04-12.22.22.000000
Result:
1898-01-03-00.00.00.000000
Input Value:
1897-12-04-12.22.22.000000
Result:
1897-01-04-00.00.00.000000
Q Quarter.

Start of changeFor ROUND_TIMESTAMP, rounds up on the 16th day of the second month of the quarter.End of change

Input Value:
1999-06-04-12.12.30.000000
Result:
1999-07-01-00.00.00.000000
Input Value:
1999-06-04-12.12.30.000000
Result:
1999-04-01-00.00.00.000000
MONTH
MON
MM
RM
Month.

Start of changeFor ROUND_TIMESTAMP, rounds up on the 16th day of the month.End of change

Input Value:
1999-06-18-12.12.30.000000
Result:
1999-07-01-00.00.00.000000
Input Value:
1999-06-18-12.12.30.000000
Result:
1999-06-01-00.00.00.000000
WW Same day of the week as the first day of the year.

Start of changeFor ROUND_TIMESTAMP, rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the year.End of change

Input Value:
2000-05-05-12.12.30.000000
Result:
2000-05-06-00.00.00.000000
Input Value:
2000-05-05-12.12.30.000000
Result:
2000-04-29-00.00.00.000000
IW Same day of the week as the first day of the ISO year.

Start of changeFor ROUND_TIMESTAMP, rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the ISO year.End of change

Input Value:
2000-05-05-12.12.30.000000
Result:
2000-05-08-00.00.00.000000
Input Value:
2000-05-05-12.12.30.000000
Result:
2000-05-01-00.00.00.000000
W Same day of the week as the first day of the month.

Start of changeFor ROUND_TIMESTAMP, rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the month.End of change

Input Value:
2000-06-21-12.12.30.000000
Result:
2000-06-22-00.00.00.000000
Input Value:
2000-06-21-12.12.30.000000
Result:
2000-06-15-00.00.00.000000
DDD
DD
J
Day.

Start of changeFor ROUND_TIMESTAMP, rounds up on the 12th hour of the day.End of change

Input Value:
2000-05-17-12.59.59.000000
Result:
2000-05-18-00.00.00.000000
Input Value:
2000-05-17-12.59.59.000000
Result:
2000-05-17-00.00.00.000000
DAY
DY
D
Starting day of the week.

Start of changeFor ROUND_TIMESTAMP, rounds up with respect to the 12th hour of the 4th day of the week. The first day of the week is always Sunday.End of change

Input Value:
2000-05-17-12.59.59.000000
Result:
2000-05-21-00.00.00.000000
Input Value:
2000-05-17-12.59.59.000000
Result:
2000-05-14-00.00.00.000000
HH
HH12
HH24
Hour.

Start of changeFor ROUND_TIMESTAMP, rounds up at 30 minutes.End of change

Input Value:
2000-05-17-23.59.59.000000
Result:
2000-05-18-00.00.00.000000
Input Value:
2000-05-17-23.59.59.000000
Result:
2000-05-17-23.00.00.000000
MI Minute.

Start of changeFor ROUND_TIMESTAMP, rounds up at 30 seconds.End of change

Input Value:
2000-05-17-23.58.45.000000
Result:
2000-05-17-23.59.00.000000
Input Value:
2000-05-17-23.58.45.000000
Result:
2000-05-17-23.58.00.000000
SS Second.

Start of changeFor ROUND_TIMESTAMP, rounds up at 500000 microseconds.End of change

Input Value:
2000-05-17-23.58.45.500000
Result:
2000-05-17-23.58.46.000000
Input Value:
2000-05-17-23.58.45.500000
Result:
2000-05-17-23.58.45.000000

Start of changeexpression is cast as follows:End of change

  • Start of changeIf expression is a timestamp with time zone value, expression is cast to a timestamp without time zone value, with the same precision as expression.End of change
  • Otherwise, expression is cast to TIMESTAMP(6) WITHOUT TIME ZONE.

Start of changeThe result of the function is a timestamp with time zone.End of change

The result can be null; if any argument is null, the result is the null value.

The result CCSID is the appropriate CCSID of the argument encoding scheme and the result subtype is the appropriate subtype of the CCSID.

Example: Set the host variable TRNK_TMSTMP with the specified date rounded to the nearest year value.
   SET :TRNK_TMSTMP = TRUNC_TIMESTAMP('2008-03-14-17.30.00', 'YEAR'); 
The host variable TRNK_TMSTMP is set with the value '2008-01-01-00.00.00.000000'.