Date Conversion 3

[notadba]

Hi,

Looking for a quick way to convert a date from say '2006-10-15' to a format of 'Oct-06'. Is there any way to do this without tedious case statements?

Using UDB for z/OS390 Version 7...

I am Sure there is a more elegant way than this:

SELECT CHAR(CASE MONTH(CURRENT DATE)
WHEN 01 THEN 'Jan'
WHEN 02 THEN 'Feb'
WHEN 03 THEN 'Mar' --etc
ELSE 'MMM' END || '-' || SUBSTR(CHAR(YEAR(CURRENT DATE)),3,2))
FROM SYSIBM.SYSDUMMY1

Thanks in advance

 

[BrianTyler]

db2 "values substr(left(monthname(d1),3)||'-'||char(year(d1)),1,8)"

The complications are there because db2 keeps trying to return a varchar field. There may be a simpler way of doing this, but it works.

Brian

 

[notadba]

Hi Brian,

What version of UDB is this in?

I am getting an "No function by the name "MONTHNAME " having compatible arguments was found in the function path."

Also was not happy with "values"

Thanks

 

[blom0344]

Both function not available for z/OS.
I think your case construction is not a bad bet.

Ties Blom

 

[MarcLodge]

Hi all,

notadba, you did say is there 'any' way of doing this on z/os and there is. You can use the month to get to the position in a predefined substring and then concatenate it to the year thus:

SELECT SUBSTR('JAN-FEB-MAR-APR-MAY-JUN-JUL-AUG-SEP-OCT-NOV-DEC-',
         MONTH(CURRENT_DATE)*4-3,4)                              
         CONCAT(SUBSTR(CHAR(CURRENT_DATE),3,2))                  
FROM SYSIBM.SYSDUMMY1

Now I'm not saying this is particularly elegant, but it is A way of doing it!

Hope this helps,
Marc

 

[blom0344]

Marc,

That is such a clever concept, have a star!

Ties Blom

 

[MarcLodge]

Why thank you Ties. I've not had internet access at work for a while, but have just got it, so it's doubly nice to get a star on my return to the forums.

Marc

 

[Mercury2]

Very clever, ingenious and inventive.

Have a new star !

 

[macehill]

Just 1 small change for us non-Americans:-

SELECT SUBSTR('JAN-FEB-MAR-APR-MAY-JUN-JUL-AUG-SEP-OCT-NOV-DEC-',MONTH(CURRENT_DATE)*4-3,4) CONCAT(SUBSTR(CHAR(YEAR(CURRENT_DATE)),3,2)) FROM SYSIBM.SYSDUMMY1

 

[MarcLodge]

Macehill,

I'm a non-American, but your code addresses a potential flaw. Depending on the default date format of an installation, the year may not be in the first 4 characters. Doing it your way will ensure the year appears at the end, regardless of format, so many thanks for spotting that.

Marc

 

[notadba]

Thanks for all of the feedback, I would never have come up with a solution like Marc's . As a half US based in Australia, will need macehill's solution. A star for you both.