Home » SQL & PL/SQL » SQL & PL/SQL » 365 days always (oracle 11g, linux)
365 days always [message #669200] Sun, 08 April 2018 07:12 Go to next message
hisham99
Messages: 106
Registered: October 2008
Location: united arab emirates
Senior Member
I need to calculate number of days between two dates, but I need the calculation to consider the year always as 365 days not 366 days. For example:

select (to_date('31/12/2014','dd\mm\yyyy')-to_date('01/01/2014','dd\mm\yyyy'))+1 from dual
(TO_DATE('31/12/2014','DD\MM\YYYY')-TO_DATE('01/01/2014','DD\MM\YYYY'))+1 
------------------------------------------------------------------------- 
365                                                                       
 
select (to_date('31/12/2012','dd\mm\yyyy')-to_date('01/01/2012','dd\mm\yyyy'))+1 from dual
(TO_DATE('31/12/2012','DD\MM\YYYY')-TO_DATE('01/01/2012','DD\MM\YYYY'))+1 
------------------------------------------------------------------------- 
366                                                                       
Re: 365 days always [message #669202 is a reply to message #669200] Sun, 08 April 2018 07:19 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
If you come up with a way of ignoring the fact that you are dealing with a leap year ("consider the year always as 365 days not 366") then your calcuation will return the wrong number of days if your period covers Feb. 29. Why do you want to do that?
Re: 365 days always [message #669203 is a reply to message #669202] Sun, 08 April 2018 07:29 Go to previous messageGo to next message
hisham99
Messages: 106
Registered: October 2008
Location: united arab emirates
Senior Member
company requirement , in asset program they need the calculation to consider year as 365 days
Re: 365 days always [message #669206 is a reply to message #669203] Sun, 08 April 2018 07:55 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
CASE?

number_of_days := case when number_of_days = 366 then 365
                       else number_of_days
                  end

This, apparently, doesn't cover situations that span through more than 1 year (OK, up to 2 years would probably be OK).
Re: 365 days always [message #669207 is a reply to message #669200] Sun, 08 April 2018 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe something like that:
SQL> with
  2    data as (
  3      select trunc(sysdate - dbms_random.value(1200,5000)) startdt,
  4             trunc(sysdate - dbms_random.value(1,1000)) enddt
  5      from dual
  6      connect by level <= 10
  7    ),
  8    dates as (
  9      select startdt, enddt, trunc(months_between(enddt,startdt)/12) full_years
 10      from data
 11    )
 12  select startdt, enddt, full_years, enddt-startdt nbdays,
 13         add_months(enddt,-12*full_years) - startdt + 365*full_years stdnbdays
 14  from dates
 15  order by 1, 2
 16  /
STARTDT     ENDDT       FULL_YEARS     NBDAYS  STDNBDAYS
----------- ----------- ---------- ---------- ----------
21-NOV-2004 18-DEC-2016         12       4410       4407
15-JAN-2005 06-JAN-2017         11       4374       4371
19-JUN-2006 07-SEP-2016         10       3733       3730
28-JUL-2007 26-FEB-2018         10       3866       3863
06-OCT-2007 17-AUG-2015          7       2872       2871
04-FEB-2010 15-JUL-2016          6       2353       2351
26-DEC-2011 29-JUL-2017          5       2042       2041
23-DEC-2012 02-SEP-2016          3       1349       1348
17-APR-2013 17-JUL-2016          3       1187       1186
10-SEP-2014 30-SEP-2015          1        385        385

10 rows selected.
Re: 365 days always [message #669208 is a reply to message #669200] Sun, 08 April 2018 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe something like that:
SQL> with
  2    data as (
  3      select trunc(sysdate - dbms_random.value(1200,5000)) startdt,
  4             trunc(sysdate - dbms_random.value(1,1000)) enddt
  5      from dual
  6      connect by level <= 10
  7    ),
  8    dates as (
  9      select startdt, enddt, trunc(months_between(enddt,startdt)/12) full_years
 10      from data
 11    )
 12  select startdt, enddt, full_years, enddt-startdt nbdays,
 13         add_months(enddt,-12*full_years) - startdt + 365*full_years stdnbdays
 14  from dates
 15  order by 1, 2
 16  /
STARTDT     ENDDT       FULL_YEARS     NBDAYS  STDNBDAYS
----------- ----------- ---------- ---------- ----------
21-NOV-2004 18-DEC-2016         12       4410       4407
15-JAN-2005 06-JAN-2017         11       4374       4371
19-JUN-2006 07-SEP-2016         10       3733       3730
28-JUL-2007 26-FEB-2018         10       3866       3863
06-OCT-2007 17-AUG-2015          7       2872       2871
04-FEB-2010 15-JUL-2016          6       2353       2351
26-DEC-2011 29-JUL-2017          5       2042       2041
23-DEC-2012 02-SEP-2016          3       1349       1348
17-APR-2013 17-JUL-2016          3       1187       1186
10-SEP-2014 30-SEP-2015          1        385        385

10 rows selected.
Re: 365 days always [message #669210 is a reply to message #669200] Sun, 08 April 2018 15:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Do you mean you want to skip February 29 when calculating days between two dates and, for example number of days between January 1 and March 1 is always 59? If so:

drop table tbl purge
/
create table tbl
  as
    select  trunc(sysdate - dbms_random.value(1200,5000)) startdt,
            trunc(sysdate - dbms_random.value(1,1000)) enddt
      from  dual
      connect by level <= 10
/
select  startdt,
        enddt,
        count(*) days365,
        enddt - startdt + 1 days
  from  tbl,
        lateral(
                select  startdt + level - 1 dt
                  from  dual
                  connect by startdt + level - 1 <= enddt
               )
  where to_char(dt,'mmdd') != '0229'
  group by tbl.rowid,
           startdt,
           enddt
/

STARTDT   ENDDT        DAYS365       DAYS
--------- --------- ---------- ----------
26-JAN-14 19-JUN-17       1240       1241
22-NOV-08 17-SEP-15       2490       2491
05-MAY-11 25-NOV-15       1665       1666
04-DEC-09 09-MAY-16       2347       2349
24-OCT-07 02-MAR-18       3780       3783
07-SEP-04 07-MAR-17       4562       4565
30-SEP-10 19-DEC-15       1906       1907
26-DEC-11 28-OCT-16       1767       1769
03-APR-07 06-DEC-17       3898       3901
01-DEC-14 06-DEC-17       1101       1102

10 rows selected.

SQL> 

SY.
Re: 365 days always [message #669211 is a reply to message #669210] Sun, 08 April 2018 15:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Missed you are on 11g:

drop table tbl purge
/
create table tbl
  as
    select  trunc(sysdate - dbms_random.value(1200,5000)) startdt,
            trunc(sysdate - dbms_random.value(1,1000)) enddt
      from  dual
      connect by level <= 10
/
select  startdt,
        enddt,
        count(*) days365,
        enddt - startdt + 1 days
  from  tbl
  where to_char(startdt + level - 1,'mmdd') != '0229'
  connect by rowid = prior rowid
         and prior sys_guid() is not null
         and startdt + level - 1 <= enddt
  group by tbl.rowid,
           startdt,
           enddt
/

STARTDT   ENDDT        DAYS365       DAYS
--------- --------- ---------- ----------
26-JAN-14 19-JUN-17       1240       1241
22-NOV-08 17-SEP-15       2490       2491
05-MAY-11 25-NOV-15       1665       1666
04-DEC-09 09-MAY-16       2347       2349
24-OCT-07 02-MAR-18       3780       3783
07-SEP-04 07-MAR-17       4562       4565
30-SEP-10 19-DEC-15       1906       1907
26-DEC-11 28-OCT-16       1767       1769
03-APR-07 06-DEC-17       3898       3901
01-DEC-14 06-DEC-17       1101       1102

10 rows selected.

SQL> 

SY.
Re: 365 days always [message #669258 is a reply to message #669211] Tue, 10 April 2018 09:17 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
A simple PL/SQL function will achieve this.

CREATE OR REPLACE FUNCTION DATE_DIFF_NOLEAP(pDate1 DATE, pDate2 DATE) RETURN NUMBER IS
   pRange NUMBER;
   x      NUMBER;
   pCnt   NUMBER;
BEGIN
   pRange := pDate2 - pDate1;
   x := 0;
   FOR pCnt in 1..pRange LOOP
      IF NOT TO_CHAR(pDate1 + pCnt, 'MM/DD') = '02/29' THEN
         x := x+ 1;
      END IF;
   END LOOP;
   RETURN x;
END;
/

SELECT DATE_DIFF_NOLEAP(TO_DATE('02/01/2016', 'MM/DD/YYYY'), TO_DATE('03/01/2016', 'MM/DD/YYYY') ) AS DAYS_NO_LEAP
FROM DUAL;

DAYS_NO_LEAP
------------
          28

JP

[Updated on: Tue, 10 April 2018 09:24]

Report message to a moderator

Re: 365 days always [message #669259 is a reply to message #669258] Tue, 10 April 2018 12:45 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
JPBoileau wrote on Tue, 10 April 2018 10:17
A simple PL/SQL function will achieve this.
You just need to add 1 to the result based on what OP asks. He said he wants 365 from Jan 1 to Dec 31. He wants total days, not number of days difference.
Re: 365 days always [message #669260 is a reply to message #669259] Tue, 10 April 2018 12:48 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
You're right, I overlooked that part. But he was already adding "+1" in his original post. My goal was to emulate (date2)-(date1) functionality while ignoring leap days.

JP

[Updated on: Tue, 10 April 2018 12:59]

Report message to a moderator

Re: 365 days always [message #669261 is a reply to message #669260] Tue, 10 April 2018 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe simple but counting million rows like that will last a bit.

Re: 365 days always [message #669264 is a reply to message #669261] Tue, 10 April 2018 16:11 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
You're correct, it's slower than molasses in January for a large result set.

I've reworked the code.

CREATE OR REPLACE FUNCTION DATE_DIFF_NOLEAP(pDate1 DATE, pDate2 DATE) RETURN NUMBER IS
   nLeaps     NUMBER := 0;
   nBeginYear NUMBER;
   nEndYear   NUMBER;
   nDays      NUMBER;
   nYear1Leap NUMBER := 0;
   nYear2Leap NUMBER := 0;
   FUNCTION ISLEAPYEAR(pLeapYear NUMBER) RETURN NUMBER IS
      nYear NUMBER;
   BEGIN
      IF MOD(pLeapYear, 4) = 0 THEN
         IF MOD(pLeapYear, 100) = 0 THEN
            IF MOD(pLeapYear, 400) = 0 THEN
               RETURN 1;
            ELSE
               RETURN 0;
            END IF;
         ELSE
            RETURN 1;
         END IF;
      ELSE
         RETURN 0;
      END IF;
   END ISLEAPYEAR;
   
   FUNCTION COUNTLEAPS(pYear1 NUMBER, pYear2 NUMBER, pYear1Leap OUT NUMBER, pYear2Leap OUT NUMBER) RETURN NUMBER IS
      nCnt NUMBER := 0;
      nPos NUMBER;
   BEGIN
      FOR nPos IN pYear1..pYear2 LOOP
         IF ISLEAPYEAR(nPos) = 1 THEN
            nCnt := nCnt +1;
            IF nPos = pYear1 THEN
               pYear1Leap := 1;
            END IF;
            IF nPos = pYear2 THEN
               pYear2Leap := 1;
            END IF;
         END IF;
      END LOOP;
      RETURN nCnt;
   END COUNTLEAPS;

BEGIN
   nBeginYear := TO_NUMBER(TO_CHAR(pDate1, 'YYYY'));
   nEndYear   := TO_NUMBER(TO_CHAR(pDate2, 'YYYY'));
   nLeaps := COUNTLEAPS(nBeginYear, nEndYear, nYear1Leap, nYear2Leap);

   -- Subtract based on the start and end dates.
   IF TO_CHAR(pDate1, 'MMDD') >= '0229' AND nYear1Leap =1 THEN
      nLeaps := nLeaps -1;
   END IF;
   IF TO_CHAR(pDate2, 'MMDD') <= '0229' AND nYear2Leap =1 THEN
      nLeaps := nLeaps -1;
   END IF;
   RETURN pDate2-pDate1-nLeaps;
END;
/


set timing on
with data as (
   select last_update_ts
   from customer
   where rownum <= 1000000)
select count(*) from data
where DATE_DIFF_NOLEAP(trunc(last_update_ts), trunc(sysdate)) <= 1000;

  COUNT(*)
----------
       983

Elapsed: 00:00:14.91

JP
Re: 365 days always [message #669272 is a reply to message #669264] Wed, 11 April 2018 09:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just create table FEB29:

create table feb29
  as
    with r(
           dt
          ) as (
                 select  date '-4712-1-1' dt
                   from  dual
                union all
                 select  dt + 1
                   from  r
                   where dt < date '9999-12-31'
               )
    select  dt
      from  r
      where to_char(dt,'mmdd') = '0229'
/
create index tbl_idx
  on tbl(dt)
/

Then:

SQL> select  count(*)
  2    from  tbl
  3  /

  COUNT(*)
----------
   1000000

Elapsed: 00:00:00.03
SQL> declare
  2      cursor v_cur
  3        is
  4          select  startdt,
  5                  enddt,
  6                  enddt - startdt + 1 - (select count(*) from feb29 where dt between startdt and enddt) days365
  7            from  tbl;
  8  begin
  9      for v_rec in v_cur loop
 10        null;
 11      end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.02
SQL> 

SY.

[Updated on: Wed, 11 April 2018 09:32]

Report message to a moderator

Re: 365 days always [message #669274 is a reply to message #669272] Wed, 11 April 2018 09:41 Go to previous message
JPBoileau
Messages: 88
Registered: September 2017
Member
Solomon Yakobson wrote on Wed, 11 April 2018 09:31
Just create table FEB29:

Yours beats mine. Well done!

dev1> set timing on
dev1> with data as (
  2     select last_update_ts
  3     from customer
  4     where rownum <= 1000000)
  5  select count(*) from data
  6  where trunc(sysdate) - trunc(last_update_ts) - (select count(*) from feb29
where dt between trunc(sysdate) and trunc(last_update_ts)  )   <= 1000;

  COUNT(*)
----------
       980

Elapsed: 00:00:01.23

dev1> set timing on
dev1> with data as (
  2     select last_update_ts
  3     from customer
  4     where rownum <= 1000000)
  5  select count(*) from data
  6  where DATE_DIFF_NOLEAP(trunc(last_update_ts), trunc(sysdate)) <= 1000;

  COUNT(*)
----------
       980

Elapsed: 00:00:15.16
Previous Topic: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Next Topic: group timestamp column hourly
Goto Forum:
  


Current Time: Thu Mar 28 10:29:06 CDT 2024