question

Jimko1-7792 avatar image
0 Votes"
Jimko1-7792 asked ·

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


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.


sql-server-generalsql-server-transact-sql
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image
1 Vote"
VaibhavChaudhari answered ·

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


· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thnk you, this also returned this error

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.

0 Votes 0 ·

Probably you have some invalid dates (other than dd/mm/yyyy) data in delivery date column

0 Votes 0 ·
OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered ·

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.

· 6 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you , when I tried this I got this message

Msg 241, Level 16, State 1, Line 20
Conversion failed when converting date and/or time from character string.

0 Votes 0 ·

Conversion failed when converting date and/or time from character string.

Then your date column isn't one, but a string column and contains values, which don't present valid dates and can't be converted.




0 Votes 0 ·

sorry should have mentioned yes, the deliverydate col is (varchar(50), but this is a training exercise set for me so there is a solution where I can perform search within this date column today -199 days. I was told by my trainer that I needed to convert deliveryDate to specific date format, deduct 199 days and compare with today’s date... im really confused now :)

0 Votes 0 ·
Show more comments
GillianMackenzie-9225 avatar image
1 Vote"
GillianMackenzie-9225 answered ·

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)    ;


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you, this is also helpful

0 Votes 0 ·