0

I have an SQL Server Query:

select *from table1 where column in ('list of values')

When I execute this, I get all the details, however, when I do this:

select *from table1 where column in ('list of values') and date_of_req='2011-03-15'

I get an empty table. All the column headings are there, but with no data. How to overcome this? I basically want to match all of those values in the IN clause and the date and display only that data.

Where am I going wrong?

2
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by axman1000
1

The SQL server stores the date along with the time. Hence, when you are writing the query like: date_of_req='2011-03-15' The server need to convert the string to date format. While doing so it will append the time as 00:00:00. So the query will return the result only when the date_of_req matches '2011-03-15 00:00:00' .

To retrieve the correct data you need to convert the date like:
SELECT * FROM table1
WHERE column in ('list of values') and CONVERT(varchar(10),date_of_req,120) = '2011-03-15'

In order to know the different conversion, please refer:
http://www.mssqltips.com/tip.asp?tip=1145
http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

Edited by Knvn: Formatted

0

Perfecto! Thanks!

The SQL server stores the date along with the time. Hence, when you are writing the query like: date_of_req='2011-03-15' The server need to convert the string to date format. While doing so it will append the time as 00:00:00. So the query will return the result only when the date_of_req matches '2011-03-15 00:00:00' .

To retrieve the correct data you need to convert the date like:
SELECT * FROM table1
WHERE column in ('list of values') and CONVERT(varchar(10),date_of_req,120) = '2011-03-15'

In order to know the different conversion, please refer:
http://www.mssqltips.com/tip.asp?tip=1145
http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.