TRUNC_TIMESTAMP scalar function

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. One greater than the first two digits of a four digit year. For ROUND_TIMESTAMP, rounds up on the 50th year of the century.	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. For ROUND_TIMESTAMP, rounds up on July 1 to January 1st of the next year.	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. For 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.	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. For ROUND_TIMESTAMP, rounds up on the 16th day of the second month of the quarter.	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. For ROUND_TIMESTAMP, rounds up on the 16th day of the month.	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. For ROUND_TIMESTAMP, rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the year.	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. For 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.	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. For ROUND_TIMESTAMP, rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the month.	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. For ROUND_TIMESTAMP, rounds up on the 12th hour of the day.	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. For 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.	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. For ROUND_TIMESTAMP, rounds up at 30 minutes.	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. For ROUND_TIMESTAMP, rounds up at 30 seconds.	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. For ROUND_TIMESTAMP, rounds up at 500000 microseconds.	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

expression is cast as follows:

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

The result of the function is a timestamp with time zone.

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.

   SET :TRNK_TMSTMP = TRUNC_TIMESTAMP('2008-03-14-17.30.00', 'YEAR');