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