Use converted date in Where clause using dynamic date statements

Question

I have successfully converted a date stored as varchar to a valid date, and now need to use it in a dynamic date statement in my Where clause. Unfortunately, I keep getting errors, or no returns. The code below runs but I get no returns, if I put a static date in, it works, and if I use the "to_date(a.sold_dt,'YYYYMMDD')" in the where clause it errors out.

select
to_date(a.sold_dt,'YYYYMMDD') as Sold_dt,
a.BUYER_ZIP_CD,
b.Statename,
b.dmaname,

COUNT(VIN) VIN_COUNT

from MyTable a
join OtherTable b
on a.Buyer_Zip_cd = b.zipcode

where sold_dt >= (Current_Date() -92)
or sold_dt (between (current_date() -457) and (current_date() - 367))
and sale_type = 'Retail'

group by 1,2,3,4

Tag your question with the database you are using. – Gordon Linoff May 08 '20 at 19:41 — Gordon Linoff, May 08 '20 at 19:41

score 0 · Answer 1 · answered May 08 '20 at 19:42

I have not idea if all your syntax works for the database you are using. But this is definitely wrong:

where sold_dt >= (Current_Date() -92) or
      sold_dt (between (current_date() -457) and (current_date() - 367)) and
--------------^ -------------------------------------------------------^
      sale_type = 'Retail'

Parens are not allowed around the between in any database that I know of.

Use converted date in Where clause using dynamic date statements

1 Answers1