Tags: convert, database, date, db2, easily, format, mysql, oracle, resultsin, sql, thetimestamp, timestamp, yyyy

Convert TIMESTAMP to DATE

On Database » DB2

3,119 words with 5 Comments; publish: Fri, 04 Jan 2008 16:25:00 GMT; (250140.63, « »)

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

All Comments

Leave a comment...

  • 5 Comments
    • whitsey wrote:
      Quote:
      === Original Words ===

      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

      #1; Fri, 04 Jan 2008 16:26:00 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')

      #2; Fri, 04 Jan 2008 16:27:00 GMT
    • On May 31, 6:41 pm, Tonkuma <tonk....db2.todaysummary.com.jp.ibm.comwrote:
      Quote:
      === Original Words ===

      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

      #3; Fri, 04 Jan 2008 16:28:00 GMT
    • See my note to teh other thread. Use INTEGER(date)

      --

      Serge Rielau

      DB2 Solutions Development

      IBM Toronto Lab

      #4; Fri, 04 Jan 2008 16:29:00 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))

      #5; Fri, 04 Jan 2008 16:30:00 GMT