| View previous topic :: View next topic |
| Author |
Message |
aniket
Beginner
Joined: 27 May 2005
Posts: 14
Topics: 8
|
 Posted: Fri Jan 27, 2006 7:19 am Post subject: Conversion of CYYMMDD format to DATE format in DB2 |
 |
|
Hi,
I want to calculate no of days difference between current date and a date field in DB2 table. This field is declared as char(7) and in CYYMMDD format. e.g. Today's date in table will be 2060127.
Currently I am using following syntax:
| Code: |
(DAYS(CURRENT DATE) -
DAYS(DATE('20'||SUBSTR(PD.WRIT_DTE,2,2)||'-'||
SUBSTR(PD.WRIT_DTE,4,2)||'-'||SUBSTR(PD.WRIT_DTE,6,2))))
|
But the problem here is that century field may either be 1 or 2 and accordingly I have to concatenate 19 or 20 in above query.
Can you please help me out in this regard? Is there any way to convert CYYMMDD format directly to DATE format?
_________________
Thanks,
Aniket. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Jan 27, 2006 9:36 am Post subject: |
|
|
aniket,
Try this . This query will take care of both 19 and 20 in the century part
| Code: |
SELECT DAYS(CURRENT DATE) -
DAYS(DATE((CASE SUBSTR(WRIT_DTE,1,1)
WHEN '1' THEN CHAR('19')
WHEN '2' THEN CHAR('20')
ELSE CHAR('20') END) ||
SUBSTR(WRIT_DTE,2,2) ||
CHAR('-') ||
SUBSTR(WRIT_DTE,4,2) ||
CHAR('-') ||
SUBSTR(WRIT_DTE,6,2)))
FROM TABLE
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
aniket
Beginner
Joined: 27 May 2005
Posts: 14
Topics: 8
|
| Posted: Fri Jan 27, 2006 10:09 am Post subject: |
|
|
Thank you very much Kolusu...
_________________
Thanks,
Aniket. |
|
| Back to top |
|
|
aniket
Beginner
Joined: 27 May 2005
Posts: 14
Topics: 8
|
| Posted: Fri Jan 27, 2006 11:27 am Post subject: |
|
|
Hi Kolusu,
I tried out with following query in SPUFI as I need 30 days difference as one of criterion:
[code:1:fc0b8af72c]
SELECT
PT.POLICY_NO
FROM
DB2PADM.VLS_POLICY_TRANS PT,
DB2PADM.VLS_POLICY_TEMP_DT PD
WHERE
PT.POLICY_NO = PD.POLICY_NO AND
PD.POLICY_NO = '4049197' AND
PT.BILL_CLASS IN ('A','B','C') AND
PT.STATUS = 49 AND
PT.FREEZE = 'A' AND
PD.WRIT_DTE
_________________
Thanks,
Aniket. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Jan 27, 2006 1:30 pm Post subject: |
|
|
Aniket,
May be WRIT_DTE has NULL values. In that case you will get an error.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
