CODEWITHSUNDEEP
sql-servert-sql
Anthony Parker
Anthony Parker

Reputation: 21

SQL Server Function Fails, logic seems good

This SQL Function returns 0 no matter what the input, where did I screw up ? The idea is to return a string or a date indicating the last day of February with a four digit year as input

CREATE FUNCTION [dbo].[LastDayOfFeb] 
----------------------------------------------------------------------------------
 --This returns the last day of February by figuring out when leap year occurs
 
--Leap years are those years that are evenly divisible by 4, except for  
--centennial years (those ending in -00), which receive the extra  
--day only if they are evenly divisible by 400 
-- Input SMALLINT , Output DATE
----------------------------------------------------------------------------------
 (@Year SMALLINT) 
returns VARCHAR

AS 
  BEGIN 
  set @year = cast(@year as smallint)
      --1. ______________Not a multiple of 4 -------------------------> NO 
      IF @Year % 4 <> 0 
        RETURN '0228' + Cast(@YEAR AS VARCHAR) 

      --2. ______________A multiple of 4 but NOT Centennial ----------> YES 
      IF @Year % 4 <> 0 
        RETURN '0229' + Cast(@YEAR AS VARCHAR)

      --3. ______________A Centennial and a multiple of 400 ----------> YES 
      IF @Year % 400 = 0 
        RETURN '0229' + Cast(@YEAR AS VARCHAR)

      --4. ______________A Centennial but NOT a multiple of 400 ------> NO 
      RETURN '0228' + Cast(@YEAR AS VARCHAR)
  END  
GO

Upvotes: 0

Views: 44

Answers (2)

T I
T I

Reputation: 9933

try

declare @year int = 2005
declare @date = dateadd(year, @year - 1900, '19000101')

select @date = dateadd(month, 2, @date)
select @date = dateadd(day, -1, @date)
select @date

rather then using strings.

as a function this would be

CREATE FUNCTION [dbo].[LastDayOfFeb]
  (@year SMALLINT)
RETURNS DATE
AS
BEGIN    
    RETURN dateadd(day, -1,
                   dateadd(month, 2, 
                           dateadd(year, @year - 1900, 0)))

END

an example of usage / test

WITH cte AS (
  SELECT year = 2000, last_day_of_feb = dbo.LastDayOfFeb(2000)
  UNION ALL
  SELECT year + 1, dbo.LastDayOfFeb(year + 1)
  FROM cte
  WHERE year + 1 <= 2040
)

SELECT *
FROM cte

SQL Fiddle

Upvotes: 0

Lee D
Lee D

Reputation: 12951

Try replacing RETURN VARCHAR with RETURN VARCHAR(10). By not specifying the size of the return string, it is assuming a length of 1, which is why you only get the leading '0'.

Upvotes: 4

Related Questions