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 |
|
arifktdm
Starting Member
6 Posts |
 Posted - 2002-12-10 : 23:05:25
|
| set @dateStart='09/01/2002'set @dateStop='11/01/2002'select case when sum([count]) is null then 0 else sum([count]) end from localPackages where [date] between @dateStart and @dateStop and ltrim(rtrim([name]))=ltrim(rtrim(@packageName)) in the above query, [date] column is of datetime type, now the problem is , if I declare @dateStart and @dateStop of type datetime it gives count as zero, if the above variables are declared as varchar then it's giving exact count.Will someone help me in this issue. |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2002-12-11 : 02:22:30
|
| Hmmm....what date format are you following? dd/mm/yyyy or mm/dd/yyyy? My guess is its a conversion problem, the dates might not be what you think they are. @dateStart might be first of Sep with a datatype of datetime, but ninth of Jan with varchar. Try testing it by changing the format of the date or using date values that are unambiguous like 12/25/2002. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-12-11 : 06:42:45
|
| You do realize that '11/01/2002' is really '11/01/2002 00:00:00.000', so '11/01/2002 00:00:00.001' is out of range and will not be counted ...Jay White{0} |
|
|
|
Thp1111
Starting Member
3 Posts |
Posted - 2002-12-11 : 17:13:39
|
| I've run into this before as well. Usually I take the upper portion of a time value (i.e., if I want between 1/1/02 and 1/15/02), i take the int of the upper date range then add 1 to get, in essence, "midnight of the next day", which guarantees that the range will be all inclusive.so you would sent your upper range to: set @dateStop=int('11/01/2002')+1this would guarantee to include any occurrances on 11/1hope this helps |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-12-11 : 21:05:11
|
| Wouldn't SQL be doing an implicit conversion on the varchar dates?I cannot reproduce the error.Sarah Berger MCSDEdited by - simondeutsch on 12/11/2002 21:09:30 |
|
|
|
|
|
|
|
|
problem with datetime column