mysql quot gt,MySQL BETWEEN和“> = AND< =”给出不同的结果

Is there any difference between MySQL BETWEEN operator and using ">= <="?

When i tried them on this query, they give different results.

SELECT

tblStaff.TitleGredCd,

count(StudCourse1.StaffNoIC),

StudCourse1.StaffNoIC,

StaffNm,

BranchNm,

StDt,

EndDt,

SUM(datediff(EndDt,StDt)+1)

TotalDay,

(SELECT SUM( pembelajaranhour )

FROM tblpembelajaran d

WHERE d.StaffNoIC = tblStaff.StaffNoIC

AND YEAR( PembelajaranDate ) = 2011 )Totalhour,

(SELECT SUM( datediff( kendiridate, kendiridate ) +1 )

FROM tblkendiri d

WHERE d.StaffNoIC = tblStaff.StaffNoIC

AND YEAR( kendiridate ) = 2011 ) Totalkendiriday

FROM

StudCourse1,

tblStaff

LEFT OUTER JOIN tblRefBranch

ON tblStaff.BranchCd = tblRefBranch.BranchCd,

tblRefTitleGred

WHERE

StudCourse1.StaffNoIC = tblStaff.StaffNoIC

AND tblStaff.TitleGredCd = tblRefTitleGred.TitleGredCd

[Date Condition]

GROUP BY

tblStaff.TitleGredCd,

StudCourse1.StaffNoIC

If I use AND StDt BETWEEN '2011-1-1' AND '2011-12-31' for [Date Condition] I get 6 returned results.

But if I use AND StDt >= '2011-1-1' AND EndDt '2011-12-31' for [Date Condition] I get 7 returned results.

Any advice would be greatly appreciated.

解决方案

BETWEEN is a bit odd with DATE types. What you are saying with your date types is

// These 2 are the same

BETWEEN '2011-01-01' AND '2011-12-31'

BETWEEN '2011-01-01 00:00:00' AND '2011-12-31 00:00:00'

and so it only match up to '2011-12-30 23:59:59', or to put it another way on a DATE field it won't match '2011-12-30'.

When you search for '2011-01-01' <= .... <= '2011-12-31' it then correctly matches the date being potentially == '2011-12-31'

So you have 4 options. The first one is to add 1 day onto the upper bound. This is the option I usually utilise The BETWEEN plays very nicely with an index on the date column, and there's no CAST or DATE() used on the data rows as the ADDDATE('2011-12-31',1) only needs executing once

WHERE `StDt` BETWEEN '2011-01-01' AND ADDDATE('2011-12-31',1)

The second is to explicitly specify the timestamp

WHERE `StDt`BETWEEN '2011-01-01 00:00:00' AND '2011-12-31 23:59:59'

The third, as you've found, is to use <= and >=

WHERE `StDt` >= '2011-01-01' AND `StDt` <= '2011-12-31'

A fourth option that also seems to work is the following, but I still prefer the first method as it's the one I know works + doesn't require using CAST or DATE on every row

WHERE DATE(`StDt`) BETWEEN DATE('2011-01-01') AND DATE('2011-12-31')

I hope this is of help