Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rushdib
Yak Posting Veteran
93 Posts |
 Posted - 2004-07-23 : 12:32:22
|
| The following two sql statements are giving two different results.Can someone explain me why? I tried using count(primarykey fieldname) still the same.select count(*) from tblproductmovement where month(adddate) = 1 and year(adddate) = 2004 select count(*) from tblproductmovement where adddate between '01/01/2004' and '01/31/2004'Thanks in advance,Rushdi |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-07-23 : 12:42:24
|
| I would suspect that it is the time component that you are missing in the second query. Perhaps you have data that is 1/31/2004 11PM, I don't think your query will capture that record. (I am assuming SQL is assuming a time of 12am).-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
 |
|
|
rlahoty
Starting Member
11 Posts |
Posted - 2004-07-23 : 13:00:59
|
| Chad is right, the second query will be like this:select count(*) from tblproductmovement where adddate between '01/01/2004 00:00:00.000' and '01/31/2004 00:00:00.000'and it will ignore all records that are inserted after 12 AM on 31st January. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 13:22:00
|
| Try using this for the second test:select count(*) from tblproductmovement where adddate >= '01/01/2004' and adddate < '02/01/2004'your BETWEEN test is only going up to midnight between 30th and 31st January, rather than midnight between 31st January and 1st FebruaryKristen |
|
|
|
rushdib
Yak Posting Veteran
93 Posts |
Posted - 2004-07-23 : 13:27:46
|
| Thanks guys. I got it now. I forgot for a while that the adddate column is a datetime data type. |
|
|
|
|
|
|
month and year function vs between in where