Solved

DATEDIFF360

Posted on 2006-07-08
7
2,618 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
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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:
ScottPletcher 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 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

13 Experts available now in Live!

Get 1:1 Help Now