SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 DATEDIFF360
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30249 Posts

Posted - 06/23/2008 :  14:05:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 06/24/2008 13:42:42

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 06/24/2008 :  05:33:25  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Sweden
30249 Posts

Posted - 06/24/2008 :  05:51:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Many questions now about Excel compatibility...



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000