Hello ... I'm trying to query th

[ks01]

Hello ...

I'm trying to query the system table SYSIBM.SYSTABAUTH and get the date and time from the fields DATEGRANTED (CHAR 6, YYMMDD) and TIMEGRANTED (CHAR 8 hhmmssth).

I tried using several scalar function combinations, but cannot seem to get the result I want. I would like to concatenate the DATEGRANTED and TIMEGRANTED fields together, but would be happy if I could just convert them over to DATE and TIME fields (I'll worry about concatenation later).

Anyone have any suggestions?

Thank you in advance ...

Kent

 

[MarcLodge]

Hi Kent,
The problem you have is that the both fields are CHAR fields rather than DATE, TIME, or TIMESTAMP format. Added to this is the fact that the data field is not CCYY but just YY.
In the example below, I've assumed that because you wish to concatenate both the date and time together, that you wish a timstamp. If that is not the case then you will need to change the SQL according to your needs. I've also added a bit of logic that says that if the year is greater than 60, then it's 1961 etc. otherwise it's 2001 etc.

SELECT CASE
WHEN SUBSTR(DATEGRANTED,1,2) > '60'
THEN TIMESTAMP('19'||SUBSTR(DATEGRANTED,1,2)||'-'||
SUBSTR(DATEGRANTED,3,2)||'-'||
SUBSTR(DATEGRANTED,5,2)||'-'||
SUBSTR(TIMEGRANTED,1,2)||'.'||
SUBSTR(TIMEGRANTED,3,2)||'.'||
SUBSTR(TIMEGRANTED,5,2)||'.'||
SUBSTR(TIMEGRANTED,7,2)||'0000')
ELSE TIMESTAMP('19'||SUBSTR(DATEGRANTED,1,2)||'-'||
SUBSTR(DATEGRANTED,3,2)||'-'||
SUBSTR(DATEGRANTED,5,2)||'-'||
SUBSTR(TIMEGRANTED,1,2)||'.'||
SUBSTR(TIMEGRANTED,3,2)||'.'||
SUBSTR(TIMEGRANTED,5,2)||'.'||
SUBSTR(TIMEGRANTED,7,2)||'0000')
END
FROM SYSIBM.SYSTABAUTH


Let me know if this does the trick.

Marc

 

[ks01]

Marc ...

Your assumption was correct, sorry I didn’t make that a bit clearer. My ideal result set would display a timestamp, but I would have settled for separate date and time fields if that wasn't possible.

I copied and pasted what you provided directly into QMF and it ran perfectly with no adjustments. I never would have thought of using the logic you did as I was not familiar with the CASE expression. I looked it up in my IBM SQL Reference guide and there is quite a bit listed about it. Thank you for sharing!

I do have a follow-up question for you; I understand your explanation regarding the year being greater than 60, but I don't understand why you chose this number. I assume it was because the field does not contain the century. If that is a correct assumption, what made you pick 60 instead of 50 or 55?

Thank you again ...

Kent

 

[MarcLodge]

Hi Kent,
There wasn't an awful lot of thought went into choosing 60 I'm afraid! I guessed at a date that I thought DB2 started (late 60's was my guess) and went back to the start of that decade. In reality I would be amazed to find a granted date/time of anywhere in the 70's or even 80's.

The CASE statement is not one that is used that often in DB2, but is very powerful. I've used it on a number of occassions in QMF to stop UNIONs etc. where you want to convert a product type to a literal.

Hope you found this helpful/expert.

Marc

 

[ks01]

Marc ...

Hi!

Yes, you've been a great help! Thanks for the follow-up explanation.

When I get done trying to figure out how I'm going to extract information from the SYS* tables for a security report (which doesn't seem to be an easy task by all means!), I'll check into the CASE expression to see what it's capable of.

Thank you again!

Kent