Hi all,
I have sql that requires the function trunc_timestamp. I am using db2 v7.1 on a windows platform.(I still use this version coz our client uses the same).This function is not in v7, is it possible to create it(so that its user defined), and how do i do that? I was also advised to move zeroes to the portion of the timestamp that would have been truncated for both comparision fields, and then do a straight compare of the timestamp(I do not know how to do this. Any idea on how to use this approach).
Thank you in advance.

simply subtract the parts of the timestamp you want to truncate.
eg:
CURRENT TIMESTAMP - MICROSECONDS(CURRENT TIMESTAMP) MICROSECONDS
truncates the microsecondpart,
CURRENT TIMESTAMP - MICROSECONDS(CURRENT TIMESTAMP) MICROSECONDS - SECONDS(CURRENT TIMESTAMP) SECONDS
truncates the seconds and so on.

there shouldn't be a problem to create a SQL scalar function that returns the truncated value

As I said in the other forum/newsgroup: DB2 doesn't have a TRUNC_TIMESTAMP function (not in V7, V8, or V9). So you should tell us what this function is supposed to do.

Thanks Umayer, thats what i wanted to do and it worked perfectly.Trunc_timestamp does the same just that its only available for z/os users.

@ stolze

TRUNC_TIMESTAMP was introduced in Version 7 of DB2 for z/OS

have a look at:
http://publib.boulder.ibm.com/cgi-bi...E=&PICTORIAL=N

Ah! Typical problem if the OP doesn't name the system he is referring to.