conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Question

I have the following piece of inline SQL that I run from a C  windows service   UPDATE table name SET      status cd    2        sdate   CAST  03 28 2011 18 03 40  AS DATETIME        bat id    33acff9b-e2b4-410e-baaf-417656e3c255        cnt   1       attempt date   CAST  03 28 2011 18 03 40  AS DATETIME   WHERE id    1855    When I run this against a SQL Server database from within the application  I get the following error      System Data SqlClient SqlException  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value    The statement has been terminated    But if I take the piece of SQL and run it from SQL Management Studio  it will run without issue   Any ideas what may be causing this issue

User · Answer

It s a date format issue  In Ireland the standard date format for the 28th of March would be  28-03-2011   whereas  03 28 2011  is the standard for the USA  among many others

User · Answer

Ambiguous date formats are interpreted according to the language of the login  This works  set dateformat mdy  select CAST  03 28 2011 18 03 40  AS DATETIME    This doesn t  set dateformat dmy  select CAST  03 28 2011 18 03 40  AS DATETIME    If you use parameterised queries with the correct datatype you avoid these issues  You can also use the unambiguous  unseparated  format yyyyMMdd hh mm ss

User · Answer

i faced this issue where i was using SQL it is different from MYSQL the solution was puting in this format   date  m-d-y h m s    rather than  date  y-m-d h m s

User · Answer

JAVA8  Use LocalDateTime now   toString

User · Answer

I know that this solution is a little different from the OP s case  but as you may have been redirected here from searching on google the title of this question  as I did  maybe you re facing the same problem I had    Sometimes you get this error because your date time is not valid  i e  your date  in string format  points to a day which exceeds the number of days of that month  e g   CONVERT Datetime   2015-06-31   caused me this error  while I was converting a statement from MySql  which didn t argue  and makes the error really harder to catch  to SQL Server

User · Answer

I think the best way to work with dates between C  and SQL is  of course  use parametrized queries  and always work with DateTime objects on C  and the ToString   formating options it provides  You better execute set datetime  lt format gt   here you have the set dateformat explanation on MSDN  before working with dates on SQL Server so you don t get in trouble  like for example set datetime ymd  You only need to do it once per connection because it mantains the format while open  so a good practice would be to do it just after openning the connection to the database  Then  you can always work with  yyyy-MM-dd HH mm ss ffff  formats  To pass the DateTime object to your parametrized query you can use DateTime ToString  yyyy-MM-dd HH mm ss ffff    For parsing weird formatted dates on C  you can use DateTime ParseExact   method  where you have the option to specify exactly what the input format is  DateTime ParseExact  lt some date string gt    dd MM-yyyy  CultureInfo InvariantCulture   Here you have the DateTime ParseExact   explanation on MSDN

User · Answer

But if i take the piece of sql and run it from sql management studio  it will run without issue    If you are at liberty to  change the service account to your own login  which would inherit your language regional perferences   The real crux of the issue is      I use the following to convert -  date Value ToString  MM dd yyyy HH mm ss     Please start using parameterized queries so that you won t encounter these issues in the future  It is also more robust  predictable and best practice

[sql] conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Examples related to sql

Examples related to sql-server

Examples related to service

Examples related to sqlexception