Change the database name
Change the script name if desire
Increase or decrease input varchar size
GetDateInString
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