In legacy and new application, date format can be a headache, simply because the date format may be stored differently and maybe in a string
value.
I find using CASE WHEN
statement to convert all my dates to the same format.
The Converting Function
This case
statement takes the table field to the SQL converter function, which needs a different style due to the known format of the string
value.
I have added a link to this blog, which shows you the different styles you can use.
CONVERT ( DATETIME , [TABLE.FIELD], SQLSTYLE)
SQL SELECT Case Statement
Below is an example where you can use a case
statement to use different convert styles, depending on the string
value format.
SELECT
CASE
WHEN DATAFIELD THEN CONVERT( DATETIME , '1900/01/01 00:00:00',103)
WHEN DATAFIELD THEN
CASE
--I KNOW WITHIN MY DATA SET, THERE IS DATE STRING WITH - AND / CHARACTERS AND THEY NEED TO BE CONVERTED DIFFERENTLY
WHEN DATAFIELD LIKE '%-%' AND ISDATE(DATAFIELD) = 1 THEN
CONVERT( DATETIME, DATAFIELD,120 )
WHEN DATAFIELD LIKE '%/%' THEN
CONVERT( DATETIME, SUBSTRING(DATAFIELD,0,11),103)
ELSE
--THE DATE 1900/01/01 IS THE FIRST DATE, I LIKE TO USE TO MAKE SURE I KNOW ITS NOT TO BE ACTED ON, BUT ITS IN THE CORRECT FORMAT
CONVERT( DATETIME, '1900/01/01 00:00:00',103)
END
ELSE
CONVERT( DATETIME, '1900/01/01 00:00:00',103)
END AS DATETEXTFIELD
FROM DATATABLE