1
select top 10  
    FormANo, Created, Changed 
from 
    FormA 
where  
    Created >= convert(datetime, '2015-07-05 14:04:11.000') 
    and Created <= convert(datetime, '2016-04-21 20:13:08.280')

when I run the query I am getting the following error

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

How can I solve this using raw SQL query in SQL Server 2014?

Md. Sabbir Ahamed
  • 435
  • 1
  • 6
  • 20
  • 4
    What is datatype of your `Created` column? – Niyoko Oct 27 '16 at 08:09
  • 1
    Both convert return proper value if you run them. Are you sure this are the values which you are converting ? – mybirthname Oct 27 '16 at 08:10
  • what is the DataType of your Created column? .Net provides you a function Convert.ToDateTime() which i think you need – Prashanth Oct 27 '16 at 08:11
  • Use Convert.ToDateTime and make sure that you are using the same Dateformats. Maybe .Net tries to convert your year to day or sth. – Sebi Oct 27 '16 at 08:13
  • 1
    Use an *unambiguous* date format. Use `T` to separate the date and time, rather than a space. SQL Server has some barking made rules where it can interpret `aaaa-aa-aa aa:aa:aa.aaa` (where `a` stands for a digit) as `yyyy-dd-MM hh:mm:ss.sss`, despite there being no culture, so far as I'm aware, that actually uses such a date format. It has no such ambiguity when presented with `aaaa-aa-aaTaa:aa:aa.aaa`. – Damien_The_Unbeliever Oct 27 '16 at 08:15
  • this might be of some use http://www.karaszi.com/SQLServer/info_datetime.asp – GuidoG Oct 27 '16 at 08:17
  • try keeping same format for both (LHS = RHS). Also, for this type of query, using between clause will be a better option – Tinu Mathew Oct 27 '16 at 08:24
  • Possible duplicate of [SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value](https://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in) – Michael Freidgeim Oct 28 '19 at 01:44

1 Answers1

6

Most likely SQL Server is trying to parse your data in another format that you are providing.

You can set the format with one of the values from this table:

convert(datetime, '2016-04-21 20:13:08.280', 121)

121 = yyyy-mm-dd hh:mi:ss.mmm(24h)

Hypnobrew
  • 1,120
  • 9
  • 23