Number of days between two dates.
Moderators: chulett, rschirm, roy
Number of days between two dates.
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
Thanks.
-Anil
-
- Participant
- Posts: 232
- Joined: Sat May 07, 2005 2:49 pm
- Location: USA
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
Hi dls,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.
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
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:
yields 67 when executed on my laptop today.
Mike
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
Mike
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Code: Select all
SELECT CAST('2005-01-01' AS DATE) - CAST('2004-12-01' AS DATE)
FROM SYSIBM.SYSDUMMY1
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
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:
Mike
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>
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
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