Monday, November 24, 2003 12:28 PM
richard
Excel's Days360 in a Sql function
I leave most of the Sql posting to Ryan but I will post this: The Excel worksheet function Days360 in a Sql function (sans the documented bug that incorrectly calculates the days for the end of February):
CREATE FUNCTION dbo.fnDays360
(
@startDate DateTime,
@endDate DateTime
)
RETURNS int
AS
BEGIN
RETURN (CASE WHEN Day(@endDate)=31 THEN 30 ELSE Day(@endDate) END) -
(CASE WHEN Day(@startDate)=31 THEN 30 ELSE Day(@startDate) END)
+ ((DatePart(m, @endDate) + (DatePart(yyyy, @endDate) * 12))
-(DatePart(m, @startDate) + (DatePart(yyyy, @startDate) * 12))) * 30
END