469,926 Members | 1,526 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,926 developers. It's quick & easy.

Formatting a Timestamp

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?

Thanks in advance!

Martin

May 4 '06 #1
4 16455
mghale wrote:
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?

Thanks in advance!

Martin


Try date() and time() functions to extract date and time portions of timestamp:

D:\Working>db2 values current timestamp

1
--------------------------
2006-05-04-19.35.48.354001

1 record(s) selected.
D:\Working>db2 values date(current timestamp)

1
----------
05/04/2006

1 record(s) selected.
D:\Working>db2 values time(current timestamp)

1
--------
19:36:23

1 record(s) selected.
D:\Working>
Jan M. Nelken
May 4 '06 #2

"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
May 5 '06 #3
Thanks so much for the replies. The iso/usa options worked perfectly
to get exactly what I was wanting returned.

Thanks again. Your help and information is greatly appreciated.

Martin

May 5 '06 #4

"mghale" <ma*********@yahoo.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...
Thanks so much for the replies. The iso/usa options worked perfectly
to get exactly what I was wanting returned.

Thanks again. Your help and information is greatly appreciated.

You're very welcome!

--
Rhino
May 5 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by RT | last post: by
4 posts views Thread by RT | last post: by
5 posts views Thread by Martin Lucas-Smith | last post: by
2 posts views Thread by WmGill | last post: by
2 posts views Thread by johndcal | last post: by
2 posts views Thread by laredotornado | last post: by
2 posts views Thread by linuxnooby | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.