In clause in Select statement

[axman1000]

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?

[Knvn]

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/

[axman1000]

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/