USE [ADN-TECHNOLOGIES-LIMITED-STAGING]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Vivek Nigam [ADN TECHNOLOGIES LIMITED]
-- Created date: 10-May-2016 @ 13:45 PM
-- Description: Sql Function to validate DATE FORMAT.
-- ISDATEFORMATCorrect returns 1 when the input expression evaluates to a valid DATE data type; otherwise it returns 0.
-- Modified By : Vivek Nigam [ADN TECHNOLOGIES LIMITED]
-- Modify On -
-- Input - Pass The DATE And Format Value Which You want to Validate
-- OutPut -
-- Return - 1 = Valid
---Return - 0 - Invalid
-- SQL Script File Name - scr_05_Function_To_Validate_Date_Format_In_Staging.sql
-- This Function will be created / excecated by Batch File Called -
01_Create_All_Functions_To_Validate_Data_After_Importing_Into_Staging.bat
--12 112 12 = yymmdd
--112 = yyyymmdd ISO
--CONVERT(varchar,GETDATE(),112) --YYYYMMDD
--CONVERT(varchar,GETDATE(),108) --HH:MM:SS
-- =============================================
--Check if a function exists on a ScribeStaging Database Before Creating - If exists Then Drop
IF object_id('dbo.fn_IsDateFormatCorrect') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[fn_IsDateFormatCorrect]
END
GO
CREATE FUNCTION dbo.fn_IsDateFormatCorrect
(
@vDate VARCHAR(80),
@vFormat VARCHAR(80)
)
RETURNS BIT
AS
BEGIN
DECLARE @Result BIT = 0
DECLARE @Dateformat INT
DECLARE @iDay INT = 0
DECLARE @iMonth INT = 0
DECLARE @iYear INT = 0
DECLARE @iCentury INT = 0
DECLARE @ihh INT = 0
DECLARE @imm INT = 0
DECLARE @iss INT = 0
IF (@vDate LIKE '%[^.:0-9 ]%') -- Only Allow 0-9 and : and 1 Space
SET @Result = 1
ELSE
SELECT @DateFormat = CASE @vFormat
WHEN 'CCYYMM' THEN 101 -- Can Not Use ISDATE() - Select ISDATE('201512') AS Date ---CCYYMM -0
WHEN 'CCYYMMDD' THEN 102 -- Use ISDATE() - -- Select ISDATE('20151223') AS Date -- CCYYMMDD - 1
WHEN 'YYMMDD' THEN 12 -- Use ISDATE() - -- Select ISDATE('151231') AS Date -- YYMMDD - 1
WHEN 'YYYMMDD' THEN 104 -- Can Not Use ISDATE() -Select ISDATE('0151223') AS Date -- YYYMMDD -0
WHEN 'YYYYMMDD' THEN 112 -- Use ISDATE() - -- Select ISDATE('20151231') AS Date -- YYYYMMDD -1
WHEN 'YYYYMMDD HH:MM:SS' THEN 108 -- Use ISDATE() - Select ISDATE('20151231 23:33:54') AS Date -- YYYYMMDD HH:MM:SS - 1
WHEN 'DD.MM.YYYY' THEN 103 -- Use ISDATE() - Select ISDATE('01.01.2012') AS Date -- DD.MM.YYYY - 1
ELSE 100
END
IF @DateFormat = 101 AND LEN(LTRIM(RTRIM(@vDate))) = 6 --- CCYYMM
BEGIN
SET @iCentury = LEFT(LTRIM(RTRIM(@vDate)),2) -- CC
SET @iYear = SUBSTRING(LTRIM(RTRIM(@vDate)),3,2) -- YY
Set @iMonth = RIGHT(LTRIM(RTRIM(@vDate)),2) -- MM
IF (@iCentury >=19 AND @iCentury <=20) AND (@iYear >=0 AND @iYear <=99) AND (@iMonth >=1 AND @iMonth <=12)
SET @Result = 0
ELSE
SET @Result = 1
END IF @DateFormat = 102 AND LEN(LTRIM(RTRIM(@vDate))) = 8 AND ISDATE (LTRIM(RTRIM(@vDate)))=1----CCYYMMDD
BEGIN
SET @iCentury = LEFT(LTRIM(RTRIM(@vDate)),2) -- CC
SET @iYear = SUBSTRING(LTRIM(RTRIM(@vDate)),3,2) -- YY
SET @iMonth = SUBSTRING(LTRIM(RTRIM(@vDate)),5,2) -- MM
Set @iDay = RIGHT(LTRIM(RTRIM(@vDate)),2) -- DD
IF (@iCentury >=19 AND @iCentury <=20) AND (@iYear >=0 AND @iYear <=99) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)
SET @Result = 0
ELSE
SET @Result = 1
END IF @DateFormat = 12 AND LEN(LTRIM(RTRIM(@vDate))) = 6 AND ISDATE (LTRIM(RTRIM(@vDate)))=1---YYMMDD
BEGIN
SET @iYear = LEFT(LTRIM(RTRIM(@vDate)),2) -- YY
SET @iMonth = SUBSTRING(@vDate,3,2) -- MM
SET @iDay = RIGHT(LTRIM(RTRIM(@vDate)),2) -- DD
IF (@iYear >=0 AND @iYear <=99) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)
SET @Result = 0
ELSE
SET @Result = 1
END IF @DateFormat = 104 AND LEN(LTRIM(RTRIM(@vDate))) = 7 ----YYYMMDD
BEGIN
SET @iYear = LEFT(LTRIM(RTRIM(@vDate)),3) -- YYY
SET @iMonth = SUBSTRING(LTRIM(RTRIM(@vDate)),4,2) -- MM
SET @iDay = RIGHT(LTRIM(RTRIM(@vDate)),2) -- DD
IF (@iYear >=900 AND @iYear <=999) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)
SET @Result = 0
ELSE
SET @Result = 1
END IF @DateFormat = 112 AND LEN(LTRIM(RTRIM(@vDate))) = 8 AND ISDATE (LTRIM(RTRIM(@vDate)))=1---YYYYMMDD
BEGIN
SET @iYear = LEFT(LTRIM(RTRIM(@vDate)),4) -- YYY
SET @iMonth = SUBSTRING(LTRIM(RTRIM(@vDate)),5,2) -- MM
SET @iDay = RIGHT(LTRIM(RTRIM(@vDate)),2) -- DD
IF (@iYear >=1900 AND @iYear <=2099) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)
SET @Result = 0
ELSE
SET @Result = 1
END IF @DateFormat = 108 AND LEN(LTRIM(RTRIM(@vDate))) = 17 AND ISDATE (LTRIM(RTRIM(@vDate)))=1-- YYYYMMDD HH:MM:SS
BEGIN
SET @iYear = LEFT(LTRIM(RTRIM(@vDate)),4) -- YYYY
SET @iMonth = SUBSTRING(LTRIM(RTRIM(@vDate)),5,2) -- MM
SET @iDay = SUBSTRING(LTRIM(RTRIM(@vDate)),7,2) -- DD
SET @ihh = SUBSTRING(LTRIM(RTRIM(@vDate)),9,3) -- HH
SET @imm = SUBSTRING(LTRIM(RTRIM(@vDate)),13,2) -- MM
SET @iss = RIGHT(LTRIM(RTRIM(@vDate)),2) -- SS
IF (@iYear >=1900 AND @iYear <=2099) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31) AND (@ihh >0 AND @ihh <=23) AND (@imm >0 AND @imm <=60) AND (@iss >0 AND @iss <=60)
SET @Result = 0
ELSE
SET @Result = 1
END IF @DateFormat = 103 AND LEN(LTRIM(RTRIM(@vDate))) = 10 AND ISDATE (LTRIM(RTRIM(@vDate)))=1---DD.MM.YYYY
BEGIN
SET @iYear = RIGHT(LTRIM(RTRIM(@vDate)),4) -- YYY
SET @iMonth = SUBSTRING(LTRIM(RTRIM(@vDate)),4,2) -- MM
SET @iDay = LEFT(LTRIM(RTRIM(@vDate)),2) -- DD
IF (@iYear >=1900 AND @iYear <=2099) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)
SET @Result = 0
ELSE
SET @Result = 1
END
RETURN @Result
END
GO
No comments:
Post a Comment