469,326 Members | 1,320 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Convert TIMESTAMP to DATE

Hi,

I can easily convert a TIMESTAMP to a DATE which will give me results
in the format 'DD/MM/YYYY' however, what I want to do is convert the
TIMESTAMP to just 'MM/YYYY' or even better 'MMM/YYYY'.

I tried using the cast function as follows but it failed.

CAST(CURRENT TIMESTAMP AS DATE FORMAT 'mm/yy')

What am I doing wrong?

Thanks

May 31 '07 #1
5 68138
whitsey wrote:
Hi,

I can easily convert a TIMESTAMP to a DATE which will give me results
in the format 'DD/MM/YYYY' however, what I want to do is convert the
TIMESTAMP to just 'MM/YYYY' or even better 'MMM/YYYY'.

I tried using the cast function as follows but it failed.

CAST(CURRENT TIMESTAMP AS DATE FORMAT 'mm/yy')

What am I doing wrong?

Thanks
It isn't pretty - and formatting for a single digit months can be
improved bu using SUBSTR, but try this:
cast(month(current timestamp) as char(2)) concat '/' concat
cast(year(current timestamp) as char(4));
Jan M. Nelken
May 31 '07 #2
Another examples are ....
1)
SUBSTR(DIGITS(MONTH(CURRENT TIMESTAMP)),9,2)
||'/'||SUBSTR(DIGITS(YEAR(CURRENT TIMESTAMP)),7,4)

2)
CAST(INSERT(CHAR(DATE(CURRENT TIMESTAMP),USA),3,3,'') AS CHAR(7) )

3)
SUBSTR(REPLACE(CHAR(DATE(CURRENT TIMESTAMP),EUR),'.','/'),4,7)

4)
TRANSLATE('FG/ABCD', CHAR(CURRENT TIMESTAMP), 'ABCD-FG')

May 31 '07 #3
On May 31, 6:41 pm, Tonkuma <tonk...@jp.ibm.comwrote:
Another examples are ....
1)
SUBSTR(DIGITS(MONTH(CURRENT TIMESTAMP)),9,2)
||'/'||SUBSTR(DIGITS(YEAR(CURRENT TIMESTAMP)),7,4)

2)
CAST(INSERT(CHAR(DATE(CURRENT TIMESTAMP),USA),3,3,'') AS CHAR(7) )

3)
SUBSTR(REPLACE(CHAR(DATE(CURRENT TIMESTAMP),EUR),'.','/'),4,7)

4)
TRANSLATE('FG/ABCD', CHAR(CURRENT TIMESTAMP), 'ABCD-FG')
THanks For that - works like a charm however, the sort order that the
dates are returned in the order of the months of the year i.e. Jan07,
Feb-07 ... Nov-06, Dec-06.

How do I sort it chronologically? i.e. Jun-06, Jul-06 ... Apr-07,
May-07

May 31 '07 #4
See my note to teh other thread. Use INTEGER(date)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 31 '07 #5
I thought that you want to get the result with format 'mm/yyyy'.
But, if you don't stic that format and want to group by and sort order
of year/month,
followings would be the answer.

1)
INTEGER(DATE(CURRENT TIMESTAMP))/100
Result: yyyymm (INTEGER)

2)
SUBSTR(CHAR(CURRENT TIMESTAMP),1,7)
Result: 'yyyy-mm' (CHAR(7))

May 31 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Bob | last post: by
4 posts views Thread by Richard Hollenbeck | last post: by
1 post views Thread by h_ghanaty | last post: by
1 post views Thread by Raja | last post: by
1 post views Thread by Sam | last post: by
4 posts views Thread by Daniel Kaseman | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.