Using Case to convert Date format in SQL Select
In legacy and new application date format can be a headache, simple because the date formate 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 us.


CONVERT ( DATETIME , [TABLE.FIELD], SQLSTYLE)


SQL SELECT Case statment
Below is an example where you can use a case statement to us 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



Created: 15/09/2016 Total Comment: 0

Comments

(Not Displayed)
Human validation
Enter 1504 number, before submitting to confirm your human
[Home] [All Blogs] [Contact] [Me]