Is this sql where statement correct?

Question

I have 10 rows for today's date but my select statement based on date dosen't seem to work....

SELECT Id,WirelessId,RegNo,DriverName1,MobileNo1,DriverName2,MobileNo1 from
 DailySchedule where IsDeleted=0 and CreatedDate='2010-05-28'

Any suggestion...

Answer 1

Assuming that CreatedDate is a datetime or a date, I don't see what's wrong with the syntax of the where clause. Maybe the IsDeleted field for those columns is actually NULL? (Or non-zero, at any rate.)

Edit: what Dave says, or DATE(CreatedDate) = '2010-05-28'

Answer 2

Only if the dates assigned to them are midnight today. It might be better to do:

CreatedDate BETWEEN '2010-05-28 00:00' AND '2010-05-29 00:00'

Answer 3

I'd check to see the type of the CreatedDate column and make sure that the string you're passing is being converted to a date properly.

When you say you 'have' 10 rows, is that confirmed visually or with a query executed in a console?

Answer 4

Not enough info to go on but I suspect that your dates have a time component and so don't match the string exactly.

Try using:

datediff(day, CreatedDate, '28-may-2010')) = 0

Answer 5

SELECT Id,WirelessId,RegNo,DriverName1,MobileNo1,DriverName2,MobileNo1 from
 DailySchedule where IsDeleted=0 and date_format(CreatedDate, "%Y-%m-%d")='2010-05-28'

Answer 6

If you want all the entries for May 28th I would do

and CreatedDate >='20100528'
and CreatedDate < '20100529'

Notice the safe ISO format (YYYYMMDD) no dashes

Also take a look at How Does Between Work With Dates In SQL Server? to see why between can not give you all the results you want

Answer 7

Can you post the results, or part of them, without where clause?

Answer 8

This shud work too !

SELECT Id,WirelessId,RegNo,DriverName1,MobileNo1,DriverName2,MobileNo1 from 
 DailySchedule where IsDeleted=0 and CONVERT(varchar,CreatedDate,101) ='05/28/2010'