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 |
|
snufse
Constraint Violating Yak Guru
469 Posts |
 Posted - 2009-10-29 : 18:46:46
|
I have a query that selects on date from / to range. I get records belonging to October when I select September. Is there a way to set the time either as 23:59:59 or 00:00:00. See below code and resultsset @DateFrom = '2009-09-01'set @DateTo = '2009-09-30'select from ......where (@DateFrom IS Null or i.NEWDATE >= @DateFrom) and (@DateTo IS Null or i.NEWDATE <= dateadd(day, 1, @DateTo)) dbeug value: print dateadd(day, 1, @DateTo) = Oct 1 2009 12:00AMRows selected are outside date from / to selection:2009-10-01 00:00:00.000 1118838 2012009-10-01 00:00:00.000 1118838 202 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-10-29 : 18:53:48
|
| (@DateTo IS Null or i.NEWDATE <= dateadd(day, 1, @DateTo)) is your problem. if you add one date to end of september you get to October and you are asking for less than or equal to October. So it is doing exactly what you asked it to do. Time to go home or coffe time.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2009-10-29 : 21:18:32
|
| Yes I figured this was the case. My question is still1. How can I set the time in my @DateTo to be 00:00:00 and @dateFrom to be 23:59:592. Do I have an option to test on date only excluding the time portionThank you. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-30 : 03:06:49
|
It should beset @DateFrom = '2009-09-01'set @DateTo = '2009-09-30'select from ......where (@DateFrom IS Null or i.NEWDATE >= @DateFrom) and (@DateTo IS Null or i.NEWDATE < dateadd(day, 1, @DateTo)) MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Select on date range