Solved

DATEDIFF360

Posted on 2006-07-08
7
2,568 Views
Last Modified: 2013-03-22
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
Comment
Question by:_JHL_
7 Comments
 
LVL 26

Expert Comment

by:DireOrbAnt
Comment Utility
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
 
LVL 26

Expert Comment

by:DireOrbAnt
Comment Utility
For DAYS360 (financial count), I guess you could do:

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

Expert Comment

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

Cheers!
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 28

Expert Comment

by:imran_fast
Comment Utility
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 125 total points
Comment Utility
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 Comment

by:_JHL_
Comment Utility
Thanks very much ScottPletcher, you have solved my problem.  Thanks again ;)
0
 

Expert Comment

by:PTBNA
Comment Utility
Watch out for Feb 28th and 29th.  I see in your sample you did not take it into account.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now