By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,664 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,664 IT Pros & Developers. It's quick & easy.

Formatting a Timestamp

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a

"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

P: n/a
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

P: n/a

"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.