Technical Article

GetDateInString

,

Change the database name

Change the script name if desire

Increase or decrease input varchar size

USE [AdventureWorks2008R2]
GO

/****** Object:  UserDefinedFunction [Global].[fn_GetDateInString]    Script Date: 12/18/2012 11:11:23 ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [Global].[fn_GetDateInString] 
( 
@InputString AS NVARCHAR(500)
)
RETURNS NVARCHAR(50)
AS 
BEGIN
DECLARE @output AS nvarchar(50)
DECLARE @StringLen AS INT = LEN(@InputString)
SET @output = CASE
-- date format as 01/01/2012
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1, 10)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1, 10) 
-- date format as 1/01/2012
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1, 9)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1, 9) 
-- date format as 01/1/2012
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9][0-9]/[0-9]/[0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1, 9)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9][0-9]/[0-9]/[0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1, 9) 
-- date format as 1/1/2012 
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9]/[0-9]/[0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1, 8)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9]/[0-9]/[0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1, 8) 
-- date format as 01/11/12
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%',@InputString),@StringLen),1, 8)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%',@InputString),@StringLen),1, 8) 
-- date format as 01/1/12
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9][0-9]/[0-9]/[0-9][0-9]%',@InputString),@StringLen),1, 8)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9][0-9]/[0-9]/[0-9][0-9]%',@InputString),@StringLen),1, 8) 
-- date format as 1/11/12
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9]/[0-9][0-9]/[0-9][0-9]%',@InputString),@StringLen),1, 7)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9]/[0-9][0-9]/[0-9][0-9]%',@InputString),@StringLen),1, 7) 
-- date format as 1/1/12
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9]/[0-9]/[0-9][0-9]%',@InputString),@StringLen),1, 6)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%[0-9]/[0-9]/[0-9][0-9]%',@InputString),@StringLen),1, 6) 
-- date format for january through december, double day digit 
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%January [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,16)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%January [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,16)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%February [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,17)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%February [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,17)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%March [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,14)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%March [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,14)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%April [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,14)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%April [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,14)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%June [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%June [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%July [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%July [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%August [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,15)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%August [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,15)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%September [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,18)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%September [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,18)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%October [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,16)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%October [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,16)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%November [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,17)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%November [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,17)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%December [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,17)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%December [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,17)
-- date format for january through december, single day digit 
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%January [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,15)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%January [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,15)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%February [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,16)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%February [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,16)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%March [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%March [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%April [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%April [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%June [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%June [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%July [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%July [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%August [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,14)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%August [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,14)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%September [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,17)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%September [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,17)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%October [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,15)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%October [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,15)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%November [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,16)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%November [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,16)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%December [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,16)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%December [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,16) 
-- date format for jan. through dec., double day digit 
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jan. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jan. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Feb. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Feb. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Mar. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Mar. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Apr. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Apr. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jun. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jun. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jul. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jul. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Aug. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Aug. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sep. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sep. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Oct. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Oct. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Nov. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Nov. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Dec. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Dec. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
-- date format for jan. through dec., single day digit 
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jan. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jan. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Feb. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Feb. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Mar. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Mar. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Apr. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Apr. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jun. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jun. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jul. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jul. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Aug. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Aug. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sep. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sep. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Oct. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Oct. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Nov. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Nov. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Dec. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Dec. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12) 
-- date format for jan through dec, double day digit 
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jan [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jan [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Feb [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Feb [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Mar [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Mar [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Apr [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Apr [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jun [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jun [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jul [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jul [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Aug [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Aug [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sep [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sep [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Oct [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Oct [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Nov [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Nov [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Dec [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Dec [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
-- date format for jan through dec, single day digit 
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jan [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jan [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Feb [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Feb [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Mar [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Mar [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Apr [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Apr [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%May [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jun [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jun [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jul [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Jul [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Aug [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Aug [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sep [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sep [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Oct [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Oct [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Nov [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Nov [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Dec [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Dec [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,11) 
-- date format for Sept. and Sept single and double digit days
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sept. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,14)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sept. [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,14)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sept. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sept. [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sept [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sept [0-9][0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,13)
WHEN ISDATE(SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sept [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)) =1
THEN SUBSTRING(SUBSTRING(@InputString,PATINDEX('%Sept [0-9], [0-9][0-9][0-9][0-9]%',@InputString),@StringLen),1,12)
ELSE
null
END 
RETURN @output
END

GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.33 (3)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.33 (3)

You rated this post out of 5. Change rating