CODEWITHSUNDEEP
sql-serverdate
Xi Vix
Xi Vix

Reputation: 1361

T-SQL statement for selecting all records on a future date

I need to select all records with a billing date 15 days from now.
This gives me nothing because it is trying to compare date and time:

 "nextbill = dateadd(d, +15, getdate())"

This works: 

"select * 
from custrate 
where nextbill >= '2014-01-02 00:00:00' and nextbill < '2014-01-02 23:59:59'"

How do I get everything with a date of 15 days from now ignoring the time?

Upvotes: 0

Views: 783

Answers (2)

user210757
user210757

Reputation: 7386

select * 
from custrate 
where convert(date,nextbill) = DATEADD(d, 15, convert(date,getdate()))

or older versions of sql:

select * 
from custrate 
where DATEADD(dd, DATEDIFF(dd, 0, nextbill), 0) = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 15)

Upvotes: -1

dotjoe
dotjoe

Reputation: 26940

I would use the start of the day with >= and start of the next day with a <...

declare @from datetime, @thru datetime;
set @from = dateadd(d, datediff(d, 0, getdate()) + 15, 0);
set @thru = dateadd(d, 1, @from);

select ...
from custrate
where nextbill >= @from and nextbill < @thru

Upvotes: 7

Related Questions