Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 DATEDIFF360

Author  Topic 

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 14:05:31
This emulates the Microsoft Excel DAYS360 function exactly.
CREATE FUNCTION dbo.DATEDIFF360
(
@source DATETIME,
@target DATETIME,
@style BIT = 0
)
RETURNS INT
AS
BEGIN
RETURN CASE @style
-- European style
WHEN 1 THEN CASE
WHEN DATEPART(DAY, @target) = 31 THEN 30
ELSE DATEPART(DAY, @target)
END
- CASE
WHEN DATEPART(DAY, @source) = 31 THEN 30
ELSE DATEPART(DAY, @source)
END
-- US style
ELSE CASE
WHEN DATEPART(DAY, @target) = 31 AND DATEPART(DAYOFYEAR, @source) = 60 AND DATEPART(MONTH, @source) = 2 THEN 30
WHEN DATEPART(DAY, @target) = 31 AND DATEPART(DAY, @source) < 30 THEN 31
WHEN DATEPART(DAY, @target) = 31 THEN 30
ELSE DATEPART(DAY, @target)
END
- CASE
WHEN @source = DATEADD(MONTH, DATEDIFF(MONTH, -1, @source), -1) THEN 30
ELSE DATEPART(DAY, @source)
END
END + 30 * DATEDIFF(MONTH, @source, @target)
END

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-24 : 05:33:25
nothing better to do again, huh peter?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 05:51:15
Many questions now about Excel compatibility...



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Koppireddy
Starting Member

1 Post

Posted - 2014-10-29 : 16:32:58
Select dbo.DATEDIFF360('2/28/2003', '3/31/2003',0)

This scenario fails. Below is the fix
SELECT
CASE
WHEN DAY(@start_date) < DAY(@end_date)
THEN DATEDIFF(MONTH, @start_date, @end_date) * 30 +
CASE WHEN Day(DATEADD(MM, DATEDIFF(MM, -1, @start_date), 0) - 1) = DAY(@start_date) THEN 0 ELSE (DAY(@end_date) - DAY(@start_date)) END
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
Go to Top of Page
   

- Advertisement -