Thanks for sharing your code but it just doesn't need to be so complex a thing. SQL Server will handle all sorts of date formats without any help. The only time it really runs into a problem is where you have DMY and MDY formats in the same column so it wouldn't be able to figure out if 3/7/2005 is in March or July in a mixed column... just like your code won't be able to.
For code that follows one standard (eithe DMY or MDY) in a given column. No special processing for format is needed. For example:
SET DATEFORMAT DMY
, ConvertedToDateTime = CAST(d.SomeStringDate AS DATETIME)
SELECT '15-03-05' UNION ALL
SELECT '15/03/08' UNION ALL
SELECT '15/03/2005' UNION ALL
SELECT '15.03.2005' UNION ALL
SELECT 'March 15 2005' UNION ALL
SELECT 'Mar 15 2005' UNION ALL
SELECT '15 March 2005' UNION ALL
SELECT '15 Mar 2005' UNION ALL
) d (SomeStringDate)
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)