SQL Server 2005 sysdate conversion

FROM: 4/13/2010 12:00:00 AM开发者_如何转开发

TO: 4/13/2010

SELECT CONVERT(varchar, EventDate, 101) AS EVENTDATE
FROM   db_Emp.dbo.tbl_EventsTable
WHERE  (EventDate BETWEEN '2010-04-01' AND '2010-04-30')

I am expecting 50 total but the results is only 10 total.

What am I doing wrong here?

The reason is that '2010-04-30' actually means 2010-04-30 00:00

Try this perhaps?

SELECT * FROM Table 
WHERE MONTH(EventDate) = 4 
AND YEAR(EventDate) = 2010

Or:

SELECT * FROM Table 
WHERE EventDate BETWEEN '2010-04-01' AND '2010-04-30 23:59:59.999'

If you're trying to get all the dates of events in April, 2010 you can do it like this.

SELECT CONVERT(varchar, EventDate, 101) 
FROM db_Emp.dbo.tbl_EventsTable
WHERE EventDate LIKE 'Apr%2010%'

Try

SELECT CONVERT(varchar, EventDate, 101) AS EVENTDATE
FROM   db_Emp.dbo.tbl_EventsTable
WHERE EventDate >= '2010-04-01' AND EventDate < '2010-05-01'