Home » SQL & PL/SQL » SQL & PL/SQL » 365 days always (oracle 11g, linux)
365 days always [message #669200] |
Sun, 08 April 2018 07:12 |
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 #669207 is a reply to message #669200] |
Sun, 08 April 2018 08:43 |
|
Michel Cadot
Messages: 68694 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 |
|
Michel Cadot
Messages: 68694 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 |
Solomon Yakobson
Messages: 3286 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 |
Solomon Yakobson
Messages: 3286 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 |
|
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 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
JPBoileau wrote on Tue, 10 April 2018 10:17A 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 #669264 is a reply to message #669261] |
Tue, 10 April 2018 16:11 |
|
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 |
Solomon Yakobson
Messages: 3286 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 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
Solomon Yakobson wrote on Wed, 11 April 2018 09:31Just 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
|
|
|
Goto Forum:
Current Time: Sat Sep 28 17:06:47 CDT 2024
|