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)'
there were some errors in the query: RequestError: Conversion failed when converting date and/or time from character string.
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)