Solved

DATEDIFF360

Posted on 2006-07-08
7
2,716 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_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17066818
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
ID: 17066828
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
ID: 17067221
Hi _JHL_,
don't forget to deal with the time component of the date columns/fields as well....

Cheers!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:imran_fast
ID: 17071049
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:
Scott Pletcher earned 125 total points
ID: 17075860
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_
ID: 17082878
Thanks very much ScottPletcher, you have solved my problem.  Thanks again ;)
0
 

Expert Comment

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

752 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