Get the Part of the Date

[titto]

Hi,
I am trying to find out, if there is an function to get the part of the date. I have a date filed in table like 2005-11-11 , i want to get the part of 2005-11 or Nov2005. Is there any DB2 Date function to get what i am looking for...
Any help is appreciated!!!

~

 

[MarcLodge]

I think that you will need to use the concatenation symbol || to get what you want.

Try something like:

SELECT SUBSTR(CURRENT DATE,1,4)||'-'|| MONTH(CURRENT DATE)

Which should give you the 2005-11 format or:

SELECT SUBSTR(MONTHNAME(CURRENT DATE),1,3)|| YEAR(CURRENT DATE)

Which should give you the Nov2005 format.

(I'm not near a DB2 machine so the code is untried - the syntax might therefore need a little tweaking)

Hope this helps,

Marc

 

[blom0344]

The easiest approach is the format YYYYMM (which I always call 'period'):

YEAR(DATE)*100+MONTH(DATE)

I have no idea if Marc' s example will work (the concatenation of non-strings)
DB2 is not very good in casting numerical values as strings...

Ties Blom
Information analyst

 

[BrianTyler]

Tiers is correct, you need further conversion

e.g.

SUBSTR(MONTHNAME(CURRENT DATE),1,3)||char(YEAR(CURRENT DATE))

SUBSTR(DIGITS(YEAR(CURRENT DATE)),7,4) || SUBSTR(DIGITS(( MONTH(CURRENT DATE) )),9,2)

Brian

 

[MarcLodge]

Can't say I didn't warn you that the SQL might need a bit of tweaking!

I also got it to work this way:
SELECT SUBSTR(CHAR(CURRENT_DATE),1,4)||'-'|| CHAR(MONTH(CURRENT_DATE))

Titto, Hope all this advice helped you to find a solution. We'd be grateful if you could let us know.

Regards,

Marc

 

[blom0344]

Brian/Marc

AFAIK the CHAR function results in a series of zero's before the actual value, like:

12345 --> 0000012345

Did IBM resolve this in the latest releases of DB2?

Ties Blom
Information analyst

 

[BrianTyler]

It seems OK now


db2 "values char(12345)"

1
-----------
12345


Brian

 

[MarcLodge]

Ties,
I've just taken the query that I wrote, added a FROM SYSIBM.SYSDUMMY1 line and run in on our mainframe under QMF 7.2 (We're on V7 of DB2 here also), and it worked fine. I ran Brian's through, and that worked fine too.

Oddly enough, I can't get the MONTHNAME function to work as it's not recognised. I've had a look in the v7 manual and it's not there, so I'm assuming that it's new with v8. Any thoughts?

Marc

 

[blom0344]

Brian,

Yes, from command line this works. Try the same with a DBA querytool and I get:

10973 --> '000000000010973'



Ties Blom
Information analyst

 

[blom0344]

Marc,

MONTHNAME was available from UDB 6.1 upwards , but not available in DB2/400.
I have little knowledge about mainframe though...

Confusing ain't it?

Ties Blom
Information analyst

 

[ddiamond]

I've found that the char function will pad zero's with decimal data types, but not with integer data types. At least this is the case with UDB 8.2. I haven't tested it with iSeries.