Count days between 2 dates

Application development
I wish to, using query count the number of days between 2 dates, the dates are in YYYMMDD format but I presume I'll need to convert the dates to Julian first to allow the caculation. Can anyone help?

Answer Wiki

Thanks. We'll let you know when a new response is added.

whenever possible I do these calculations in SQL server. Using SQL server, the syntax is simple. DB2 is a bit more complex. Here are examples in both.

SQL Server:
select convert(datetime, convert(varchar, fieldname + 19000000)) from …

SELECT date(substr(digits(fieldname + 19000000),2,4)||’-‘||substr(digits(fieldname + 19000000),6,2)||’-‘|| substr(digits(fieldname + 19000000),8,2)) FROM library/file

I’m assuming you’re YYYMMDD is 990227 for 1999 and 1070227 for 2007, and that the dates are stored in a numeric field. if they’re in a string field things are a bit more complex, but not impossible. Once you have the fields in a datetime format, you can use normal date comparison/arithmetic on them.

Hope that helps,

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • WaltZ400
    If you are using SQL and your dates are stored in true date format in your data base, you can use the DAYS function. This translate a date into a numeric value that you subtract from another. An example is: Select Days(enddate) - Days(fromdate)from file Where Days(enddate) - Days(fromdate) > 10
    655 pointsBadges:
  • TomLiotta
    Since there are multiple kinds of "query" available, it'd be far easier to come up with an answer if you told us which kind of query you're running. OPNQRYF? Query/400? QM query? RPG embedded SQL? Other? Tom
    125,585 pointsBadges:
  • bvining
    Using CL the PowerCL command Retrieve Duration (RTVDURXCL) can return the number of days between two date values. The RTVDURXCL command directly supports the format YYYMMDD (though the actual specification is *CYMD). The command supports a wide variety of date formats and several duration calculations (days, months, years, minutes, etc). Bruce Vining (one of the PowerCL developers)
    7,070 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: