0

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
2
Contributors
1
Reply
14
Views
2 Years
Discussion Span
Last Post by Taywin
0

For this type, using match with regular expression should be faster. You may look at how to do it here. However, it all depends on your experience in regex as well.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.