SQL server filtering from Today date and X number of days prior

Question

Hi all im pretty new to SQL.

This is the current simple query, which returns the carrier with lowest number of haulage shipments in the uk for a particular brewery.

USE Rep_UK

SELECT count(DISTINCT Load_ID) AS STO
 ,carrierName
FROM dbo.main_table_uk AS main
WHERE Origin_Location_ID LIKE 'GB%'
 AND Destination_Location_ID LIKE 'GB%'
GROUP BY carrierName
ORDER BY STO

I need to in addition to this, also filter by a date range which needs to be 199 days previous from the current (today) date.

We have a 'delivery date' table which contains dates in the following format dd/mm/yyyy.

I understand I need to likely convert this first to INT then minus 199 from current date but cannot work out how to do it, any help much appreciated!

If I missed something please let me know!

using SQL server.

EDIT: I tried to use WHERE deliveryDate < DateAdd(dd, -199, GetDate()) but I got an error

---

EDIT found what I was looking for by adding

WHERE convert(DATE, main.deliveryDate, 103) >= GETDATE()-199

this worked and did not return error, many thanks for all help.

Answer 1

Try adding below like where clause

Where convert(date,YourDateCol , 103)< cast(GETDATE() as date) and YourDateCol > DATEADD(DAY,-199, convert(date,GETDATE(), 103))

Also see below example -

select DATEADD(DAY,-199, convert(date, '09/03/2021', 103))

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

If you get an error message, then please post the full error message.

This should work

SELECT count(DISTINCT main.Load_ID) AS STO
      ,main.carrierName
FROM dbo.main_table_uk AS main
WHERE main.Origin_Location_ID LIKE 'GB%'
      AND main.Destination_Location_ID LIKE 'GB%'
      AND main.deliveryDate BETWEEN DATEADD(day, -199, CONVERT(date, GETDATE())) AND CONVERT(date, GETDATE())
GROUP BY main.carrierName
ORDER BY STO

Keep in mind, that GETDATE() returns a time portion, which can cause wrong results; that's why I convert it to "date" without time.

Answer 3

Does this help?

-- firstly, for test purposes, build a table of delivery dates in required format
create table #temp ( mydate varchar(50));

declare @d date;
set @d = '20200101';

declare @s char(10);

while @d <= cast(getdate() as date)  
begin
    set @s = convert(varchar(50), @d, 103);
    insert into #temp values (@s);
    set @d = dateadd(d,1,@d);
end

-- check character dates are in format required
select mydate from #temp;

-- check expression
select dateadd(day,-199,cast(getdate() as date));

-- check where clause
select mydate from #temp 
    where mydate between convert(varchar(50),dateadd(day,-199,cast(getdate() as date)),103)
    and cast(getdate() as date) ;