View previous topic :: View next topic
|
Author |
Message |
boopathy.gopalsamy
New User
Joined: 29 May 2006 Posts: 2 Location: India
|
|
|
|
suppose the date 1 = 30Oct06
then date 2 should be 30Nov06.
Is there any function available? |
|
Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006 Posts: 3156 Location: Tucson AZ
|
|
|
|
boopathy.gopalsamy wrote: |
suppose the date 1 = 30Oct06
then date 2 should be 30Nov06. |
But suppose the date 1 = 30Jan06
then date 2 should be 30Feb06??
You might want to explore this thread http://www.ibmmainframes.com/viewtopic.php?t=16678&highlight=13+months
Quote: |
Is there any function available? |
Not really, the tricky part is what are you going to do for the exceptions as noted above.... |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
boopathy.gopalsamy,
Did you ever get a resolution to your question? |
|
Back to top |
|
|
abhijeet3162002
New User
Joined: 05 Jul 2004 Posts: 6 Location: mumbai
|
|
|
|
You can convert your date in dd-mmmm format where dd will be day of the month ( between 1 to 31) and mmmm will be months since 1900.
( both variables will be numeric).
you can advance month by just adding 1 to mmmm variable. you can convert this date in mm-dd-yyyy format by just divind mmmm by 12.
reminder will be month ( for reminder 0 month will be december) and 1800 + quotient will give you exact year.
Hope I am clear.
Cheers, |
|
Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006 Posts: 3156 Location: Tucson AZ
|
|
|
|
abhijeet3162002 wrote: |
You can convert your date in dd-mmmm format where dd will be day of the month ( between 1 to 31) and mmmm will be months since 1900.
( both variables will be numeric).
you can advance month by just adding 1 to mmmm variable. you can convert this date in mm-dd-yyyy format by just divind mmmm by 12.
reminder will be month ( for reminder 0 month will be december) and 1800 + quotient will give you exact year. |
But suppose the date 1 = 30Jan06
then date 2 should be 30Feb06?? |
|
Back to top |
|
|
abhijeet3162002
New User
Joined: 05 Jul 2004 Posts: 6 Location: mumbai
|
|
|
|
Yeah...
you r right...
I ll think on it..
Thanks |
|
Back to top |
|
|
raghunathns
Active User
Joined: 08 Dec 2005 Posts: 127 Location: rochester
|
|
|
|
try this query..
SELECT (CURRENT DATE + 1 MONTH) FROM SYSIBM.SYSDUMMY1
replace current date with your input date. |
|
Back to top |
|
|
raghunathns
Active User
Joined: 08 Dec 2005 Posts: 127 Location: rochester
|
|
|
|
SELECT (DATE('2006-01-31') + 1 MONTH) FROM SYSIBM.SYSDUMMY1
will result 2006-02-28 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Before you write any code or use any built-in date functions, you will want to get a definition of what "+1 month" is in your organization. Once the rule has been defined, you can then implement your code.
As W.T. mentioned, there is another similar thread in this forum. |
|
Back to top |
|
|
raghunathns
Active User
Joined: 08 Dec 2005 Posts: 127 Location: rochester
|
|
|
|
you can use + 1 month in a query. it is not an organization standard. its a db2 standard. |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
If this was only a DB2 question!!!..... |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Yes, you can use + 1 month in a query. That does NOT mean the result will be acceptable in all organizations. As widely used as DB2 is, it does not yet dictate business rules.
If they have db2
Quote: |
If this was only a DB2 question!!!.....
|
and if the result is correct for that organization, great - if not other means should be used for this calculation. |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
Dick is right,
Every organization can be different, especially when it comes to fiscal weeks, months, and years. As an example, an organizations fiscal month could start on the first Sunday on the calendar month, putting the start of the fiscal year, first month and week on January 7, 2007.
Probably, the DB2 algorithm will be correct though, but as Dick said, you need to check.
And you now need to be a DB2 program |
|
Back to top |
|
|
|