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?
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/
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/
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.