• Status: Solved
• Priority: Medium
• Security: Public
• Views: 3119

DATEDIFF360

Hi Experts,

I want some help about this, I know that DATEDIFF (dd,start_date,end_date) returns me the number of days between those dates, but they are based on the 365(366) days of each year. Now I need the same function but based on 360 days, just like the excel function "DAYS360", is there any way to do it?

Thanks

JHL
0
_JHL_
1 Solution

Commented:
As far as I know, DATEDIFF is based on "actual days" not a static 365. It takes two dates and returns the actual # of days (in your example) between the two.
0

Commented:
For DAYS360 (financial count), I guess you could do:

DATEDIFF(MONTH, @start_date, @end_date) * 30 + DAY(@end_date) - DAY(@start_date)

0

Commented:
Hi _JHL_,
don't forget to deal with the time component of the date columns/fields as well....

Cheers!
0

Commented:
Hi ,

DATEDIFF (dd,start_date,end_date)  will give you number of days between start date and end date

i think what you want is to have no of months between two dates and then multiply it by 30
DATEDIFF (m,start_date,end_date) * 30 + DATEDIFF (dd, cast (year(end_date) as varchar(4)) + cast (month(end_date) as varchar(2)) +'01',end_date)
0

Senior DBACommented:
This will take some relatively complex logic!

I think the code below should be pretty close, although I have not attempted to fully duplicate the ending date logic as defined by Excel help [the starting date logic, as I understand it, is implemented].  Can work further on the ending date logic if you can provide a specific example of dates and differences between SQL and Excel:

DECLARE @start_date DATETIME
DECLARE @end_date DATETIME

SET @start_date = '20060130'  --try also with '20060131'
SET @end_date = '20060202'

SELECT CASE WHEN DAY(@start_date) < DAY(@end_date)
THEN DATEDIFF(MONTH, @start_date, @end_date) * 30 + (DAY(@end_date) - DAY(@start_date))
ELSE (DATEDIFF(MONTH, @start_date, @end_date) - 1) * 30 + (30 - (CASE WHEN DAY(@start_date) = 31 THEN 30 ELSE DAY(@start_date) END) + DAY(@end_date)) END

Note that Jan 30 and Jan 31 *should* return the same value in this case! [at least the way I read the Excel Help it should :-) .]
0

Author Commented:
Thanks very much ScottPletcher, you have solved my problem.  Thanks again ;)
0

Commented:
Watch out for Feb 28th and 29th.  I see in your sample you did not take it into account.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.