Tags: convert, database, date, db2, easily, format, mysql, oracle, resultsin, sql, thetimestamp, timestamp, yyyy
Convert TIMESTAMP to DATE
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?
Leave a comment...
- 5 Comments
- whitsey wrote:Quote:
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 ...
CAST(INSERT(CHAR(DATE(CURRENT TIMESTAMP),USA),3,3,'') AS CHAR(7) )
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.questionfor.info.jp.ibm.comwrote:Quote:
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)
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
followings would be the answer.
Result: yyyymm (INTEGER)
Result: 'yyyy-mm' (CHAR(7))#5; Fri, 04 Jan 2008 16:30:00 GMT