SQL Server DateDiff

Question

I have a SQL Server query that I need to return all dates that are less that 90 days from the current. I thought this was quite trivial until the result set returned was completely wrong. Here's the query.

SELECT new_HwWarrantyEndDate 
FROM TABLE 
WHERE new_HwWarrantyEndDate IS NOT null 
  AND DATEDIFF(day,GETDATE(),new_HwWarrantyEndDate) <= 90;

Here are some of the results:

new_HwWarrantyEndDate
---------------------
2010-07-11
2012-12-09
2011-02-12
2012-12-09
2007-12-31
2007-12-31
2007-12-31
2007-12-31

How could this function return dates from years previous?

I have another issue.Why would a query such as:

SELECT DATEDIFF(day,GETDATE(),new_HwWarrantyEndDate) AS DateDiff 
FROM TABLE 
WHERE Diffdate IS NOT null;

Complain specifically DateDiff is not a valid column when I try and process the resultset ie:

result_set = stmt.executeQuery(query);
Date s;
if(!result_set.next()) {
System.out.println("Null set");
}
while(result_set.next()){
s = result_set.getDate("DateDiff");
System.out.println(s);
}

Thanks I don't have much experience with SQL Server. Any guidance would help.

Also (1) you can't reference an alias from the select clause in where, from, etc. (2) you named the alias datediff then tried to reference diffdate. — Aaron Bertrand, Jan 25 '14 at 16:12

score 4 · Accepted Answer · edited May 23 '17 at 12:11

4

In SARGable form;

SELECT new_HwWarrantyEndDate
FROM TABLE
WHERE  new_HwWarrantyEndDate IS NOT NULL
AND new_HwWarrantyEndDate BETWEEN GETDATE() AND DATEADD(DAY, 90, GETDATE())

edited May 23 '17 at 12:11

Community

1
1

answered Jan 25 '14 at 15:50

MarkD

5,276
1
14
22

thanks buddy how is that different than dateAdd(day,getdate,90) ? – Bgreen Jan 25 '14 at 16:39
The function expects parameters as described in BOL (DATEADD (datepart , number , date ) )http://technet.microsoft.com/en-us/library/ms186819.aspx – MarkD Jan 25 '14 at 16:41
Ahh! I thought I couldn't use DATEADD() BC it returns a date & I wasn't sure if sqlserver could compare dates in WHERE clause – Bgreen Jan 25 '14 at 17:03
1

That still gave me dates that were in 2015,2016 what worked for me was. `String query = "SELECT new_HwWarrantyEndDate FROM TABLE WHERE new_HwWarrantyEndDate IS NOT null AND" +" new_HwWarrantyEndDate BETWEEN GETDATE() AND DATEADD(day,90,GETDATE());";` – Bgreen Jan 25 '14 at 17:48
I'm sooo sorry, my mistake! Didn't see that you wanted to look 90 days ahead - thought 90 days ago. Will edit my code now. – MarkD Jan 25 '14 at 20:05

SQL Server DateDiff

1 Answers1