I am writing SSIS packages and at one point I have to check a date field. It may come in any format of the following but I need it to pass any valid date the faster the better of course. Always returns as a varchar
Acceptable formats 'mddyyyy', 'mmddyyyy', 'mm/dd/yyyy', 'm/d/yyyy'
return a date in the 'm/dd/yyyy' or 'mm/dd/yyyy' format
declare @datadate varchar(10)
SET @datadate = '07/23/2013'
SET @datadate = LTRIM(RTRIM(@datadate))
SET @datadate = case
when isdate(@datadate) = 1 then @datadate
when ((len(@datadate) = 7) AND (ISDATE(STUFF(STUFF(STUFF(@datadate,1,0,'0'),3,0,'/'),6,0,'/'))) = 1) then
STUFF(STUFF(STUFF(@datadate,1,0,'0'),3,0,'/'),6,0,'/')
when (len(@datadate) = 8 AND ISDATE(STUFF(STUFF(@datadate,3,0,'/'),6,0,'/')) = 1) then STUFF(STUFF(@datadate,3,0,'/'),6,0,'/')
else NULL
end