Click here to Skip to main content
15,845,785 members
Articles / Programming Languages / SQL

Using Case to convert Date format in SQL Select

Rate me:
Please Sign up or sign in to vote.
3.11/5 (4 votes)
29 Sep 2016CPOL 21.3K   3   3
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 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionHard to understand Pin
Wendelius29-Sep-16 10:50
mentorWendelius29-Sep-16 10:50 
QuestionFormatting issues with several blog posts Pin
Wendelius28-Sep-16 5:13
mentorWendelius28-Sep-16 5:13 
GeneralRe: Formatting issues with several blog posts Pin
Thomas Cooper28-Sep-16 5:53
Thomas Cooper28-Sep-16 5:53 
Hi Mike

I've just got code project to start consuming my blogs RSS feed.
This seems to be an issue with the feed as you go to the main blog, it fine.

I'll try and have a look and once I've come to the bottom of this, I will update this question.

Again Thanks

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.