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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-12 : 08:37:46
|
| Karunakaran writes "Hi, I want some datas of the users who have registered with me between date1 and date2.I tried the following queries but it didnt gave me any answers.Select count(userids) from table1 where dateofreg between 01/11/2001 and 30/11/2001Select count(userids) from table1 where dateofreg between 01-11-2001 and 30-11-2001I should get the count as 621.Instead I got the answer as 0.Please help me to solve this problemRegards,Karunakaran" |
|
|
mono
Starting Member
36 Posts |
Posted - 2001-12-12 : 08:48:48
|
Put the date in quotes or (better IMO) use convert(datetime, '30/11/2001', 103) to ensure the date is correctly interpreted.I haven't tested it but it would not suprise me if 01-11-2001 were evaluated to -2011 ;-)If your dateofreg is char that could be the problem. It would need to be similarly converted.hth, |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-12 : 11:18:11
|
| 01/11/2001 will perform the division arithmetic, take the decimal number as a reult and convert it to a date.It will do this in integer values so end up with 0 which is '1 jan 1900'You expression will becomebetween '1 jan 1900' and '1 jan 1900'similaly01-11-2001 = -2011 = 30 jun 1894try between '20011101' and '20011130'note that this will still miss everything on the day of 20011130.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Comparison of dates