SQL Server Date format Issue

Question

I have this query to display records based on date

Select * 
  From orders 
 Where CONVERT(VARCHAR(50), Orderdate, 101) = CONVERT(VARCHAR(50),'1/21/2010', 101) 

Though there are orders for today, the query is failing because the date passed is not 01/21/2010 How can I fix this issue in the query itself as the date passed by the other application is m/d/yyyy if the date and month are less than 10

Answer 1

Don't convert the dates to strings, compare them when they're still of the datetime datatype. If you want to disregard of time of day when comparing, you should convert both to 00:00:00 of that day:

FLOOR(CONVERT(float, OrderDate)) = FLOOR(CONVERT(float, CONVERT(DateTime, '1/21/2010')))

Answer 2

Try this:

SELECT * FROM orders where CONVERT(VARCHAR(50),Orderdate,101) = CONVERT(varchar(50), CONVERT(datetime,'1/21/2010'),101)

This assumes orderdate is a datetime.

Answer 3

For simplicity avoid using convert function until you have to display it to the screen and use ANSI standard 'YYYY-MM-DD HH:MM:SS' format for datetime.

SELECT * 
FROM orders 
WHERE Orderdate = '2010-01-21 00:00:00'

Answer 4

I strongly suggest that you start using the universal format for Sql Server to avoid this problem from the root and get clear code :)

Try with this:

Select * 
  From orders 
 Where Orderdate = '20100121'

The format is always yyyyMMdd

Cheers

Answer 5

What is the datatype for Orders.Orderdate? What is a value from that column? Once we have that we might be able to provide a solution to your problem.