Search code examples
sql-serverdatediff

Get result of last six months using DATEDIFF

I'm trying to create a query in SQL that retrieves rows based on their date.

I want to get the result of the last 6 months using DATEDIFF() function (and not another function ) but my query still returns rows that are greater than GETUTCDATE().

The query that I use is: 

SELECT * FROM CARS
WHERE DATEDIFF(d, c.ExpiredWarranty, GETUTCDATE()) < 180

Why am i still getting results that are greater than GETUTCDATE() ?

Solution

  • Because if ExpiredWarranty > GETUTCDATE() then DATEDIFF between them returns a negative number which is definitely less then 180.

    Try:

    SELECT *
FROM CARS
WHERE DATEDIFF(d, c.ExpiredWarranty, GETUTCDATE()) < 180
  AND DATEDIFF(d, c.ExpiredWarranty, GETUTCDATE()) >= 0;

    Or:

    SELECT *
FROM CARS
WHERE DATEDIFF(d, c.ExpiredWarranty, GETUTCDATE()) BETWEEN 0 AND 180;