Using Case to convert Date format in SQL Select

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