"mghale" <ma*********@yahoo.com> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...
I have a question that I'm hoping has an easy answer.
I'm working in DB2 V8.2 on AIX 5.3
I have a timestamp column (i.e. 4/26/2006 1:02:42.000000 PM) that I
want to return in a report from standard SQL.
The user does not want to see it as a timestamp. They want to see just
a regular date and time. When I convert to CHAR I get back the wrong
format (i.e. 2006-05-04-15.56). What I really want back is the
following:
2006-05-04 3:56 PM
Is there a built in date and/or time function that returns that result?
You can use a combination of built-in functions to get the desired result;
just concatenate the different parts together to get the format you want.
Something like this, assuming the column containing the timestamp is called
tstamp1:
select char(date(tstamp1),iso), char(time(tstamp1),usa)
from mytable
where....
In the expression 'char(date(tstamp1), iso)', the date() function obtains
the date portion (year, month, and day) of the timestamp; the char()
function with the 'iso' argument tells DB2 to format the date using the ISO
standard, i.e. 4 digit year, 2 digit month, 2 digit day, separated by
dashes.
In the expression 'char(time(tstamp1), usa)', the time() function obtains
the time portion of the timestamp; the char() function with the 'usa'
argument tells DB2 to format the time using the US standard, i.e. hours,
followed by a colon, minutes, followed by a space and AM or PM.
--
Rhino
