Calculating How Many Days Are Between Two Dates in Db2

Calculating how many days are between two dates in DB2?

I think that @Siva is on the right track (using DAYS()), but the nested CONCAT()s are making me dizzy. Here's my take.

Oh, there's no point in referencing sysdummy1, as you need to pull from a table regardless.

Also, don't use the implicit join syntax - it's considered an SQL Anti-pattern.

I'be wrapped the date conversion in a CTE for readability here, but there's nothing preventing you from doing it inline.

WITH Converted (convertedDate) as (SELECT DATE(SUBSTR(chdlm, 1, 4) || '-' ||
                                               SUBSTR(chdlm, 5, 2) || '-' ||    
                                               SUBSTR(chdlm, 7, 2))
                                   FROM Chcart00
                                   WHERE chstat = '05')

SELECT DAYS(CURRENT_DATE) - DAYS(convertedDate)
FROM Converted

How to calculate days between two date columns in db2?? how do i correct the difference of days in this query?

You are close. Use the DAYS() function to compute an integer for each date, then subtract them.

The query should look like:

SELECT CD1
, OD1
,CHAR(DATE(SUBSTR(CHAR(CD1),1,4) ||'-'||
    SUBSTR(CHAR(CD1),5,2) ||'-'||
   SUBSTR(CHAR(CD1),7,2)), USA)
AS "CDate_Conversion"
,CHAR(DATE(SUBSTR(CHAR(OD1),1,4) ||'-'||
    SUBSTR(CHAR(OD1),5,2) ||'-'||
   SUBSTR(CHAR(OD1),7,2)), USA)
AS "OConv",

( DAYS(DATE(
    SUBSTR(CHAR(CD1),1,4) ||'-'||
    SUBSTR(CHAR(CD1),5,2) ||'-'||
    SUBSTR(CHAR(CD1),7,2)), USA)
 -
  DAYS(DATE(
    SUBSTR(CHAR(OD1),1,4) ||'-'||
    SUBSTR(CHAR(OD1),5,2) ||'-'||
    SUBSTR(CHAR(OD1),7,2)), USA)
) AS Days

FROM PDAT.ZPKD

Difference in Days in DB2

It would possibly help if we knew what version of DB2 you were using and what platform it was running on. But it seems likely that you can do something like this:

select
      days(my1stdate) - days(my2nddate) as myDuration
from mySchema.myTable

The DAYS() function converts a DATE value into the number of days between Jan 1, 0001, and the supplied DATE value. Once both DATEs are converted, the subtraction (difference) is straightforward.

count the no of sundays between two dates in db2

I'm not sure of what you mean in your code.

If you have values you're trying to lookup from a database the most sustainable way would be to create a function that you could call. If this is not doable as a result of your permissions / etc. you can take a look at the below SQL:

WITH DATE_EXAMPLES (DATE_1, DATE_2) AS (
    VALUES ( TIMESTAMP('2012-03-04 01:01:01'),TIMESTAMP('2012-03-25 01:01:01'))
)
SELECT  DATE_EXAMPLES.*, 
    CASE WHEN DAYOFWEEK(DATE_2) < DAYOFWEEK(DATE_1) 
            THEN    ((DAYS(DATE_2) - DAYS(DATE_1)) / 7) + 1
            ELSE    ((DAYS(DATE_2) - DAYS(DATE_1)) / 7)
    END SUNDAYS_BETWEEN        
FROM DATE_EXAMPLES

*Please note: The DAYOFWEEK call will return a number range of 1-7 where 1 is Sunday and 7 is Saturday.

Count number of weekdays from the dates passed in where clause in DB2

Is this the correct result, if you run it as is?

with table_name (date) as 
(
  values
  '01-01-2019'
, '01-01-2019'
, '02-01-2019'
, '03-01-2019'
, '04-01-2019'
, '05-01-2019'
, '06-01-2019'
, '07-01-2019'
, '08-01-2019'
, '09-01-2019'
)
select 
—- distinct date
count(distinct date)
from table_name
where dayofweek_iso(date(to_date(date, 'DD-MM-YYYY'))) < 6

Calculate SQL db2 date time difference between below

Try one of these Db2 functions

DAYS_BETWEEN    (h,l)
HOURS_BETWEEN   (h,l)
MINUTES_BETWEEN (h,l)
MONTHS_BETWEEN  (h,l)
SECONDS_BETWEEN (h,l)
WEEKS_BETWEEN   (h,l)
YEARS_BETWEEN   (h,l)
YMD_BETWEEN     (h,l)

The functions return whole numbers. Put the higher value first to get a +ve number out.

---

Related Topics