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
_JHL_Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor 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
 
DireOrbAntCommented:
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
 
DireOrbAntCommented:
For DAYS360 (financial count), I guess you could do:

DATEDIFF(MONTH, @start_date, @end_date) * 30 + DAY(@end_date) - DAY(@start_date)
 
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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

Cheers!
0
 
imran_fastCommented:
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
 
_JHL_Author Commented:
Thanks very much ScottPletcher, you have solved my problem.  Thanks again ;)
0
 
PTBNACommented:
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.

All Courses

From novice to tech pro — start learning today.