Tuesday, 7 January 2014

Function to validate Date Format in SQL Server




As part of the validation process, sometimes we may require to validate the date format according to the business requirement. If we need to validate this activity in many places, then instead of using same repeated scripts, we can simply develop a user defined function which makes our life easier.

Below script is used to validate whether date format is in “MON-YYYY” format. And year should be between 1947 and 2100.

This script can be customized in many ways according to the requirement.

Function Code:

IF OBJECT_ID ('UDF_IsDateFormat','FN') IS NOT NULL
   DROP FUNCTION UDF_IsDateFormat;
GO
CREATE FUNCTION dbo.UDF_IsDateFormat(@Value VARCHAR(100))
RETURNS BIT
AS
BEGIN
 
 DECLARE @Month VARCHAR(30),
                                                @Year VARCHAR(30),
                                                @Spl_Char VARCHAR(1),
                                                @Result BIT

 SELECT @Month              =         SUBSTRING(@Value, 1, 3),
                                      @Year                                 =         SUBSTRING(@Value,5,4),
                                      @Spl_Char =         SUBSTRING(@Value,4,1)

 IF @Month NOT IN ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')  OR CONVERT(INT, @Year) < 1947 OR CONVERT(INT, @Year) > 2100 OR @Spl_Char <> '-'
                        SELECT @Result = 0
ELSE
                        SELECT @Result = 1

RETURN @Result

END

Execution Result:

SELECT dbo.UDF_IsDateFormat('Dec-2013')

-- 1

SELECT dbo.UDF_IsDateFormat('Wel-2013')

-- 0