Day Count (no: of days between 2 dates) [message #227269] |
Tue, 27 March 2007 16:47 |
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 #227993 is a reply to message #227269] |
Fri, 30 March 2007 10:21 |
flyboy
Messages: 1903 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 #228001 is a reply to message #227269] |
Fri, 30 March 2007 11:16 |
flyboy
Messages: 1903 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>
);
|
|
|