Problem with leap year in MS SQL giving different results for same year
I'm having a strange problem. I've got a function dbo.ufn_GetDaysInYear which seems to give correct results if I pass it year by hand dbo.ufn_GetDaysInYear('2008')
. But if I don't but I pass it like dbo.ufn_GetDaysInYear(YEAR(@dataKoncowa))
it gives different result for same year.
DECLARE @dataNaDzien DATETIME
SET @dataNaDzien = '20080101'
DECLARE @dataPoczatkowa DATETIME
DECLARE @dataKoncowa DATETIME
DECLARE @iloscDniRok INT
SET @dataPoczatkowa = DATEADD(dd, 0, DATEDIFF(dd, 0, @dataNaDzien))
SET @dataKoncowa = @dataPoczatkowa + '23:59:59'
SET @iloscDniRok = dbo.ufn_GetDaysInYear(YEAR(@dataKoncowa))
SELECT @dataKoncowa -- Result 2008-01-01 23:59:59.000
SELECT YEAR(@dataKoncowa) -- Result 2008
SELECT dbo.ufn_GetDaysInYear(YEAR(@dataKoncowa)) -- Result 365
SELECT dbo.ufn_GetDaysInYear('2008') --Result 366
Here are functions:
USE [Baza]
GO
/****** Object: UserDefinedFunction [dbo].[ufn_IsLeapYear] Script Date: 03/04/2011 11:17:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufn_IsLeapYear] ( @pDate DATETIME )
RETURNS BIT
AS
BEGIN
IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
RETURN 1
RETURN 0
END
USE [Baza]
GO
/****** Object: UserDefinedFunction [dbo].[ufn_GetDaysInYear] Script Date: 03/04/2011 11:17:34 ******/
SET ANSI_NULLS ON
GO
SET QU开发者_高级运维OTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufn_GetDaysInYear] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
RETURN 365 + [dbo].[ufn_IsLeapYear] ( @pDate )
END
What Am I missing?
Year() function returns value of INT datatype. Your function expects DATETIME value
精彩评论