| Author |
Topic |
|
rtown
Yak Posting Veteran
53 Posts |
 Posted - 2013-12-31 : 10:56:43
|
I have a datediff to pull all records due within 7 days. It was pulling records with a negative value as well, meaning past due which I obviously dont want.I threw an ABS on there, as I understand this will only retrieve positive value, but it is still pulling negative value dates. Maybe ABS is not what I though it was...WHERE ABS(DATEDIFF(DAY, CONVERT(Date, GETDATE(), 103), CONVERT(Date, DwgDue, 103))) < 7 Maybe there is something else I am missing here? Getting 7 days till due, and 7 days overdue in my results.Thanks! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-12-31 : 12:00:28
|
WHERE dwgDue >= GETDATE() AND dwgDue <= DATEADD(DAY, 7, GETDATE());
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-31 : 13:14:55
|
quote:
Originally posted by rtown
I have a datediff to pull all records due within 7 days. It was pulling records with a negative value as well, meaning past due which I obviously dont want.I threw an ABS on there, as I understand this will only retrieve positive value, but it is still pulling negative value dates. Maybe ABS is not what I though it was...WHERE ABS(DATEDIFF(DAY, CONVERT(Date, GETDATE(), 103), CONVERT(Date, DwgDue, 103))) < 7 Maybe there is something else I am missing here? Getting 7 days till due, and 7 days overdue in my results.Thanks!
seehttp://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2014-01-02 : 11:56:39
|
| Thanks Visakh, I ended up using a Between which solved the issue and allows more control. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-02 : 12:12:21
|
quote:
Originally posted by rtown
Thanks Visakh, I ended up using a Between which solved the issue and allows more control.
BETWEEN works so long as your date field doesnt have timepart. otherwise it has a very little chance of some rows getting excluded/included------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2014-01-02 : 17:28:19
|
quote:
Originally posted by visakh16BETWEEN works so long as your date field doesnt have timepart. otherwise it has a very little chance of some rows getting excluded/included
Ya I always use dates as a 10 character text field. I have had so many problems in the past using the sql datetime field. So no problems there.Thanks again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-03 : 00:48:17
|
quote:
Originally posted by rtown
quote:
Originally posted by visakh16BETWEEN works so long as your date field doesnt have timepart. otherwise it has a very little chance of some rows getting excluded/included
Ya I always use dates as a 10 character text field. I have had so many problems in the past using the sql datetime field. So no problems there.Thanks again.
but that would make the date field non sargable and would make optimizer ignore indexes if present on datetime field------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2014-01-03 : 13:32:38
|
quote:
Originally posted by visakh16but that would make the date field non sargable and would make optimizer ignore indexes if present on datetime field
Yes I suppose that's true... seems too late now. |
|
|
|
|
Datediff ABS function not working as intended