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 |
|
SirFang
Starting Member
6 Posts |
 Posted - 2005-06-17 : 17:21:05
|
| How can I use greater than and less than with dates?I am trying to count records with a date higher or lower than what I put in.If I run SELECT COUNT(*) AS Expr1, PPWEDateFROM ronros.TechWorkOrdersGROUP BY PPWEDateHAVING (PPWEDate = CONVERT(DATETIME, '2005-05-14 00:00:00', 102))I get a number 6382Great, but if I change the = to a < or > I get garbageThanks |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-17 : 22:54:59
|
| I'm not sure about the garbage result, but CONVERT 102 is used to format DATETIME to VARCHAR. As you have it coded, it's converting VARCHAR to DATETIME... maybe someone else has an idea about the result, but it looks wrong to me.You might do better withHAVING PPWEDate = CAST('May 14 2005' AS DATETIME)But you don't mention the datatype of the column PPWEDate anywhere. PPWEDate might be DATETIME, or VARCHAR, and if it's VARCHAR, how are the dates and time stored? |
 |
|
|
SirFang
Starting Member
6 Posts |
Posted - 2005-06-18 : 12:51:26
|
| The dates are stored as datetime. The code I posted was the autogenerated code from Enterprise manager. Thanks for the help. With what you posted, I am now getting an itemized list of each date and how many entries there are. I sure with some trial and error I can total the list. thanks again |
|
|
|
|
|
|
Compare dates