Deepankar Agrawal Deepankar Agrawal - 4 months ago 18
Node.js Question

there were some errors in the query: RequestError: Conversion failed when converting date and/or time from character string

i am using MS SQL Server 2008 when i run this query

select top 1 * from table_name where ModifiedDate='Wed Mar 16 2016 15:52:20 GMT+0530 (IST)'


i get this error

there were some errors in the query: RequestError: Conversion failed when converting date and/or time from character string.


i also tried
CAST
and
CONVERT
but having same error

i am using node mssql client

Answer

To answer the question directly, use this instead:

select top 1 * from table_name where ModifiedDate=cast('2016-03-16 15:52:20 +05:30' as datetimeoffset)

Now the explanation. To use timezones, you need to use datetimeoffset - regular datetime fields don't include timezone info. So, this will work:

select cast('2016-7-16 15:52:20 +05:30' as datetimeoffset)

Using AdventureWorks2012 (this syntax will also work in sql 2008, but this is demoing how you'd write this query against the AW2012 database):

select top 1 * from Person.Address where ModifiedDate=cast('2002-01-04 00:00:00 +00:00' as datetimeoffset)

Note in this case, AW2012 uses a datetime field, but not a datetimeoffset field in this table - nevertheless, it compiles and runs.

The next example demonstrates how the timezone offset works:

select top 1 * from Person.Address where ModifiedDate=cast('2002-01-04 04:00:00 +04:00' as datetimeoffset)

This returns a value, whereas changing the offset by 1 hour returns no result. (There's one record with a ModifiedDate of 2002-01-04 00:00:00 in the table):

select top 1 * from Person.Address where ModifiedDate=cast('2002-01-04 04:00:00 +03:00' as datetimeoffset)