Number of days between two dates.

[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

[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

[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.

[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

[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.

[anil411]

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

[anil411]

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

[anil411]

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

[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

[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)

[Sainath.Srinivasan]

Cast them as integer and perform the compulation.

[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

[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

[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)

[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