CODEWITHSUNDEEP
sqldatetimesql-server-2008-r2
user240141
user240141

Reputation:

Datetime to varchar conversion error

I know tons of such questions are already here on SO, but my question is slightly different than questions that already exist.

Scenario: Database has ApptDt column of type DateTime, with values in format "yyyy-mm-dd hh:mm:ss". I am from India and dates are passed in European Format, "dd-mm-yyyy". So every time I am getting this error:

The Conversion of a Varchar Datatype to a datetime results in an out of range value

Sample Query 1: 

  Declare @EffectiveDt as varchar(29)
  Set @EffectiveDt = '27/07/2013'
  print Convert(DateTime,@EffectiveDt,102) // throws above error

Sample Query 2:

Declare @EffectiveDt as varchar(29)
Set @EffectiveDt = '07/27/2013'    
print Convert(DateTime,@EffectiveDt,104) // throws above error too

Question:

  1. The two formats are valid and conversions are allowed in T-Sql; why are such errors thrown?

  2. Are there any generic functions or scenarios to hold such to-and-from conversions inside SQL?

Upvotes: 0

Views: 1076

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280590

What you should be doing is NOT using regional formats like d/m/y or m/d/y in the first place. If you use an unambiguous string format like yyyymmdd then there will never be an issue and you don't have to find all kinds of crazy workarounds. Just format your dates in a clear and standard way in the first place.

Just because you're from India does not mean you have to use regional strings to represent dates. If you're letting people enter dates in free text (which is the only way I could explain you ended up with 07/27/2013, 27/07/2013 and 27-07-2013), stop doing that, or validate their input. If someone enters 05/06/2013, how are you going to know if they mean May 6th or June 5th? SQL Server can't tell either, and there is no magic way (like there is in Access) to force it to guess, and transpose the numbers if they aren't valid. That is actually pretty scary behavior.

Upvotes: 2

Mike Perrenoud
Mike Perrenoud

Reputation: 67918

Just CAST it instead of trying to CONVERT it in a very specific culture:

SELECT CAST(@EffectiveDt AS DATETIME)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460288

Change 102 to 104 (german format)

SELECT Convert(DateTime,@EffectiveDt,104)

Demo

Upvotes: 0

Related Questions