Convert TIMESTAMP to DATE

[whitsey]

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

[Jan M. Nelken]

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

[Tonkuma]

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

[whitsey]

On May 31, 6:41 pm, Tonkuma <[e-mail]:

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

[Serge Rielau]

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Tonkuma]

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