Convert dd-mm-yyyy to mm/dd/yyyy

Question

I have a varchar(200) column called Submit_Date and I am trying to convert it to MM/DD/YYYY format. When I do that I get the following error:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Sample Data of the table is:

Submit_Date
-----------------------
27-09-2013 16:15:00 CST
30-09-2013 16:30:24 CST
27-09-2013 10:03:46 CST

I tried the following:

Select Convert(datetime,Submit_date,101) from dbo.Tickets

Answer 1

You've committed about 15 cardinal sins about date/time here. First, the quick answer:

DECLARE @x VARCHAR(200);

SELECT @x = '27-09-2013 16:15:00 CST'

SELECT CONVERT(CHAR(10),CONVERT(DATETIME,LEFT(@x,10),105),101);

Next:

1. Why on earth are you storing date/time data in a varchar(200) column? You are aware that anyone can insert values like '09-27-2013' or '465-32-207floob' in there, right? If you need time zone information you can look at the DATETIMEOFFSET data type (but note that it is not DST-aware).

2. Why are you storing a regional format like dd-mm-yyyy? If the first value were 07-11-2013 I'd have to guess if you meant July 11 or November 7. If you're not going to do it right and use a proper date/time data type, why use a string format that makes people guess? You are much better off with a format that is unambiguous, such as yyyy-mm-ddThh:mm:ssZ.

3. Similarly, why are you outputting a different regional format like mm/dd/yyyy? If you output '05/06/2013' are you 100% confident that everyone in your audience will know you meant May 6 and not June 5? Your output should be unambiguous as well. If you absolutely must format in some regional and ambiguous format, use the string formatting capabilities of your client. For example, C# has .ToString() and .Format() which are much more powerful and efficient in presenting dates with string formats that T-SQL will ever be.