Home » SQL & PL/SQL » SQL & PL/SQL » Day Count (no: of days between 2 dates)
Day Count (no: of days between 2 dates) [message #227269] Tue, 27 March 2007 16:47 Go to next message
mach460
Messages: 5
Registered: March 2007
Junior Member
HI ALL,

Is there a simple CASE statement/expression to count the number of days between 2 dates in a table. I have three columns:

AS_OF_Date, Issue_DT, ACTL_MTY_DT

I need a day count statement which calculates :

AS_of_date - (either ISSue_Dt or ACTL_MTY_DT depending on which columns is populated).

Thanks in advance...any help is appreciated

Andy

[Updated on: Tue, 27 March 2007 16:48]

Report message to a moderator

Re: Day Count (no: of days between 2 dates) [message #227272 is a reply to message #227269] Tue, 27 March 2007 18:36 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
simple minus will do that
for example days since the beginning of the year:

SQL> select trunc(sysdate) - to_date(trunc(sysdate, 'yyyy')) from dual;

                                           86


Gints Plivna
http://www.gplivna.eu
Re: Day Count (no: of days between 2 dates) [message #227305 is a reply to message #227269] Tue, 27 March 2007 23:27 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
I think this is what you have in mind.

select 
case when ISSue_Dt is null then (AS_of_date - ACTL_MTY_DT )
     when ACTL_MTY_DT is null then (AS_of_date - ISSue_Dt )
     else ACTL_MTY_DT 
end

from <your_table)


Razz

Re: Day Count (no: of days between 2 dates) [message #227359 is a reply to message #227305] Wed, 28 March 2007 01:48 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

or you can check for coalesce function.


regards,
Re: Day Count (no: of days between 2 dates) [message #227366 is a reply to message #227359] Wed, 28 March 2007 01:56 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select as_of_date - nvl(ISSue_Dt,ACTL_MTY_DT) from table;
Re: Day Count (no: of days between 2 dates) [message #227986 is a reply to message #227305] Fri, 30 March 2007 09:40 Go to previous messageGo to next message
mach460
Messages: 5
Registered: March 2007
Junior Member
Asherfine....

Thanks for the CASE statement input.....it seem to have worked but one small issue. How do I make the day count between 2 dates calculate on a 30/360 day basis.....

Thanks in advance.....
Re: Day Count (no: of days between 2 dates) [message #227987 is a reply to message #227269] Fri, 30 March 2007 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 22802
Registered: January 2009
Senior Member
stop changing the requirements.
Re: Day Count (no: of days between 2 dates) [message #227993 is a reply to message #227269] Fri, 30 March 2007 10:21 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
Hi,
now the query returns number of days between dates.
I do not know, what '30/360 day basis' should represent. For eg. 360 days, what number do you want to return? 1? 12? Something different?
If you decide anyhow, then your task is to get eg. 12 from 360. In this case, what about using division by 30?
Re: Day Count (no: of days between 2 dates) [message #227997 is a reply to message #227993] Fri, 30 March 2007 10:36 Go to previous messageGo to next message
mach460
Messages: 5
Registered: March 2007
Junior Member
flyboy...

30/360 day basis...we assume 360 days in a year and 30 days every month.....

for example in DECember my AS_OF_DT gonna be '31-DEC-06'
but in while caluclating the difference it should assume 30 days....

Thanks
Re: Day Count (no: of days between 2 dates) [message #228001 is a reply to message #227269] Fri, 30 March 2007 11:16 Go to previous message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
I see it now. Should have searched at Wikipedia.

So use the process described there under 30/360. Use the help of SQL functions.
Either directly substitute
yy(Date) -> TO_CHAR(Date, 'YYYY') or EXTRACT (year FROM Date)
mm(Date) -> TO_CHAR(Date, 'MM') or EXTRACT (month FROM Date)
dd(Date) -> TO_CHAR(Date, 'DD') or EXTRACT (day FROM Date)

or use MONTHS_BETWEEN (untested)
SELECT (months_between( trunc(Dateb, 'MM'), trunc(Datea, 'MM') )*30 + extract(day FROM Dateb) - extract(day FROM Datea)) / 360
FROM (
  SELECT <get_the value from appropriate date using the definition in link using CASE> Datea, <dtto> Dateb
  FROM <your_table>
);
Previous Topic: Finding missing sequences
Next Topic: number(currency) format
Goto Forum:
  


Current Time: Tue Sep 23 05:44:33 CDT 2014

Total time taken to generate the page: 0.13305 seconds