Convert TIMESTAMP to DATE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

whitsey - 31 May 2007 08:01 GMT

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

Reply to this Message

Jan M. Nelken - 31 May 2007 08:23 GMT

> Hi,
>
[quoted text clipped - 9 lines]
>
> 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

Reply to this Message

Tonkuma - 31 May 2007 09:41 GMT

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')

Reply to this Message

whitsey - 31 May 2007 11:35 GMT

> Another examples are ....
> 1)
[quoted text clipped - 9 lines]
> 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

Reply to this Message

Serge Rielau - 31 May 2007 12:17 GMT

See my note to teh other thread. Use INTEGER(date)

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Tonkuma - 31 May 2007 13:35 GMT

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))

Reply to this Message