Page 1 of 2
Number of days between two dates.
Posted: Thu Aug 11, 2005 12:36 pm
by anil411
Hi, I am trying to calculate number of days between two dates. (Also numbers of days from a particular date till today). I am using db2 on mainframe OS/390. Both dates are stored in db2 table as date type fields. Please help.
Thanks.
-Anil
Posted: Thu Aug 11, 2005 12:40 pm
by ketfos
Hi,
Convert both the dates using Iconv function to internal date format.
This is a number.
Subtract the two numbers.
Use Oconv function to convert the this number into a date format.
Ketfos
Posted: Thu Aug 11, 2005 3:14 pm
by pnchowdary
Hi Anil,
How about the sdk Built-in transform DateGenericDateDiff. I am not sure whether it works on MVS edition, but give it a try.
Posted: Thu Aug 11, 2005 6:04 pm
by Mike
Hi Anil,
Mainframe jobs don't have much in the way of date manipulation transformations. Your best bet is to do your date subtractions within DB2 during your extraction process.
Mike
Posted: Thu Aug 11, 2005 7:11 pm
by dls
In case you want to do the date arithmetic in DS390, you should become familiar with the CAST functions.
E.g., cast the dates as integers. The difference will be the number of days.
Posted: Tue Aug 16, 2005 5:42 am
by anil411
pnchowdary wrote:Hi Anil,
How about the sdk Built-in transform DateGenericDateDiff. I am not sure whether it works on MVS edition, but give it a try.
Hi Naveen,
DateGenericDateDiff does not work on MVS edition.
Thanks,
Anil
Posted: Tue Aug 16, 2005 5:47 am
by anil411
Mike wrote:Hi Anil,
Mainframe jobs don't have much in the way of date manipulation transformations. Your best bet is to do your date subtractions within DB2 during your extraction process.
Mike
Hi Mike,
At present, it is being done within db2 but I want to remove that step, if it is possible to calculate date difference in DataStage.
Thanks,
Anil
Posted: Tue Aug 16, 2005 5:48 am
by anil411
dls wrote:In case you want to do the date arithmetic in DS390, you should become familiar with the CAST functions.
E.g., cast the dates as integers. The difference will be the number of days.
Hi dls,
I am familiar with cast. If I cast dates as integer the difference will not be the number of days. For example number of days between December 1, 2004 and January 1, 2005 are 31 but if I convert these dates to integers and subtract, I don't get a meaningful answer. Can you please explain further, how I can do this.
Thanks,
Anil
Posted: Tue Aug 16, 2005 6:55 am
by Mike
Hi Anil,
It would be possible to do within a mainframe DataStage job, but IMO, that would be much less desirable than doing it within DB2.
For a mainframe job, you will have to hand-code a COBOL subroutine that does the date difference. You would then use an external routine stage to integrate the subroutine into your mainframe job. An external routine operates at a record level (as opposed to a field level), so to do two different date differences would require two external routine stages.
If more robust transformation capability is required, then you should consider doing the transformations in a server job and leave the mainframe job for simple extraction and filtering.
You can use the DAYS builtin function of DB2 to obtain a date difference in number of days.
For example:
Code: Select all
SELECT DAYS(CURRENT DATE) - DAYS(DATE('2005-06-10')) AS DAYS_DIFFERENCE
FROM SYSIBM.SYSDUMMY1
yields 67 when executed on my laptop today.
Mike
Posted: Tue Aug 16, 2005 8:11 am
by dls
I don't have access to mainframe and DS390.
What is the result when you derive DAYS_DIFF as:
CAST('2005-01-01' AS DATE) - CAST('2004-12-01' AS DATE)
Posted: Tue Aug 16, 2005 12:14 pm
by Sainath.Srinivasan
Cast them as integer and perform the compulation.
Posted: Tue Aug 16, 2005 7:38 pm
by Mike
Code: Select all
SELECT CAST('2005-01-01' AS DATE) - CAST('2004-12-01' AS DATE)
FROM SYSIBM.SYSDUMMY1
returns a date duration value of 100 (interpreted as 1 month and 0 days).
Subtracting two date columns will return a date duration as a DECIMAL(8) number that you interpret as the difference in years, months and days. The difference is "yyyymmdd".
Code: Select all
SELECT CAST( CAST('2005-01-01' AS DATE) AS INTEGER) - CAST( CAST('2004-12-01' AS DATE) AS INTEGER)
FROM SYSIBM.SYSDUMMY1
returns 8900, which is the same as 20050101 - 20041201
Mike
Posted: Thu Aug 18, 2005 6:41 am
by Mike
Anil,
What you're attempting to do just isn't possible in a mainframe job. After the dates are read into a mainframe job, they are CHAR(10) COBOL data items. You can't do arithmetic on CHAR data items. You need to do it in DB2 as part of your SELECT SQL statement.
For example:
Code: Select all
SELECT CASE
WHEN (DAYS(EFF_DT) > DAYS(CURRENT DATE))
THEN DAYS(EFF_DT) - DAYS(CURRENT DATE)
ELSE
DAYS(CURRENT DATE) - DAYS(EFF_DT)
END AS DIF
FROM <TABLE>
Mike
Posted: Thu Aug 18, 2005 6:43 am
by dls
I'm curious.
Did you try what I suggested? I.e.:
What is the result when you derive DAYS_DIFF as:
CAST('2005-01-01' AS DATE) - CAST('2004-12-01' AS DATE)
Posted: Fri Aug 19, 2005 10:50 am
by Mike
Anil,
The SQL that I gave you does not go in a stage variable derivation inside a transformer. It needs to be incorporated into your select statement within the source relational stage. You will probably have to edit the SQL since I doubt that the GUI will support the syntax.
There is nothing that you can do in a transformer stage (it just isn't capable). You have to use a relational stage (best) or an external routine stage (not recommended).
Mike