Wednesday, November 24, 2010

Format date from MM/DD to MM/DD/YYYY

CREATE FUNCTION [dbo].[FORMATEDATE](


@INPUT VARCHAR(100),

@FORMAT VARCHAR(100)

)

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @DAY VARCHAR(100),@MONTH VARCHAR(100),@YEAR VARCHAR(100)



IF @INPUT IS NOT NULL

BEGIN

IF(UPPER(@FORMAT) = 'MM/DD')

BEGIN

SET @MONTH = SUBSTRING(@INPUT,0,CHARINDEX('/',@INPUT))

SET @DAY = SUBSTRING(@INPUT,CHARINDEX('/',@INPUT)+1,LEN(@INPUT))



IF(@MONTH > MONTH(GETDATE())) SELECT @YEAR = YEAR(GETDATE())

ELSE IF(@MONTH = MONTH(GETDATE()) AND @DAY >= DAY(GETDATE())) SELECT @YEAR = YEAR(GETDATE())

ELSE SELECT @YEAR = YEAR(GETDATE()) + 1



IF(@MONTH = 2 AND @DAY > 28)

BEGIN

IF (SELECT DBO.[ufn_IsLeapYear](@YEAR)) <> 1

BEGIN

SET @YEAR = @YEAR + 4-@YEAR%4

END

END

SET @INPUT = @MONTH + '/' + @DAY + '/' + @YEAR

END

END

RETURN @INPUT



END

GO

1 comment: