| View previous topic :: View next topic |
| Author |
Message |
ace
Beginner
Joined: 24 Feb 2004
Posts: 58
Topics: 23
|
 Posted: Thu Aug 11, 2005 10:29 am Post subject: Adding a day to the invalid date. |
 |
|
Hi
I'm getting some invalid dates in the format YYYY-MM-DD. I need to add a day to this date and make it a valid date.
For example:
2005-06-31 should be changed to 2005-07-01
2005-02-31 should be changed to 2005-03-01
I'm using DB2 date function to check the invalid date like
| Code: |
SELECT DATE(:WS-INPUT-DATE)
INTO :WS-OUT-DATE
FROM SYSIBM.SYSDUMMY1
|
If it returns -181 SQLCODE this is an invalid date. If so I need to add a day to it and make it a valid date. I know this can be done by checking the month and add one to month and then set day to 01. Also if it's December add 1 to year.But is there any built in function available for it?
Tried SET DATE but I think it expect a valid date as input
Appreciate any help on this.
_________________
Thank You
-Ace |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Aug 11, 2005 11:05 am Post subject: |
|
|
ace,
try this untested code
| Code: |
MOVE SPACES TO WS-VAL-DATE
MOVE '2005-06-31' TO WS-INV-DATE
EXEC SQL
SET :WS-VAL-DATE = DATE(:WS-INV-DATE)
END-EXEC
EVALUATE SQLCODE
WHEN +0
CONTINUE
WHEN -181
MOVE '01' TO WS-INV-DATE(9: 2)
EXEC SQL
SET :WS-VAL-DATE = DATE(LAST_DAY(:WS-INV-DATE) + 1 DAY)
END-EXEC
DISPLAY WS-VAL-DATE
END-EVALUATE
|
Hope this helps...
Cheers
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
ace
Beginner
Joined: 24 Feb 2004
Posts: 58
Topics: 23
|
| Posted: Thu Aug 11, 2005 2:00 pm Post subject: |
|
|
Thanks Kolusu
It worked...But one doubt. I'm getting the valid date as MM/DD/YYYY format where as my invalid date is YYYY-MM-DD.
_________________
Thank You
-Ace |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Aug 11, 2005 2:14 pm Post subject: |
|
|
| Quote: |
It worked...But one doubt. I'm getting the valid date as MM/DD/YYYY format where as my invalid date is YYYY-MM-DD.
|
ace,
When your DB2 admin installed DB2 at your shop, there is an application programming defaults panel screen where he specifies values to be used as default values by the program preparation panels, the program preparation CLIST (DSNH), and the precompiler. They can also be used as defaults by other programs, such as Query Management Facility (QMF) and spufi also.
The date format can be ISO(yyyy-mm-dd), JIS(yyyy-mm-dd), USA(mm/dd/yyyy) , EUR( dd.mm.yyyy), LOCAL(your choice)
Depending on the default date format , DB2 validates the dates when inserting/updating a date field.
Now if your shop has defined USA format as the default date format , you will get the output in USA format.
check this link for Application programming defaults panel 2: DSNTIP4
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/DSNIGH13/2.4.19?DT=20030515110323
So I suggest that you talk to your DBA and find out if you can override the pre-complier option.
Hope this helps...
Cheers
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
