Help with SQL date calculations

I am using the shazam report wizard to generate a report to give me a list of chemicals that are expired.

Here is what I have so far.

SELECT MAIN.NameRaw,
         LOCATES.Location,
         MAIN.NameSorted,
         BATCH.UniqueContainerID,
         MAIN.ID,
         MAIN.CASNo,
         MAIN.StructuralFormula,
         MAIN.IsSupplyItem,
         BATCH.Quantity,
         SIZETYPE.Size_Description,
         BATCH.Expiration
    FROM ":cisdata:MAIN.DB" MAIN,
         ":cisdata:BATCH.DB" BATCH,
         ":cisdata:LOCATES.DB" LOCATES,
         ":cisdata:SIZETYPE.DB" SIZETYPE
   WHERE ((MAIN.ID = BATCH.ID)
     AND (BATCH.SizeTypeID = SIZETYPE.SizeTypeID)
     AND (BATCH.LocationID = LOCATES.LocationID))
     AND (((BATCH.Expiration < "03/30/2006")))
ORDER BY MAIN.NameRaw ASC,
         LOCATES.Location ASC,
         MAIN.NameSorted ASC

The date "03/30/2006" is actually shown in the defined search as <TODAY.  I guess it just gives the "calculated value" when I actually look at the SQL code.

How could I modify the code to give me anything that expires within 30 days?  I tried <TODAY + 30 but that returns and error.

browneye9000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

raviberiCommented:
Use GETDATE()+30
HuyBDCommented:
Try this!

DATEADD (day, 30, GETDATE())

It return new date(today+30days)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
browneye9000Author Commented:
Neither of these worked.  Shazam report wizard can be difficult from what I understand.  I get a type mismatch error for either of these.

After entering in the QBE, this is what it writes at the SQL.

SELECT MAIN.NameRaw,
         LOCATES.Location,
         MAIN.NameSorted,
         BATCH.UniqueContainerID,
         MAIN.ID,
         MAIN.CASNo,
         MAIN.StructuralFormula,
         MAIN.IsSupplyItem,
         BATCH.Quantity,
         SIZETYPE.Size_Description,
         BATCH.Expiration
    FROM ":cisdata:MAIN.DB" MAIN,
         ":cisdata:BATCH.DB" BATCH,
         ":cisdata:LOCATES.DB" LOCATES,
         ":cisdata:SIZETYPE.DB" SIZETYPE
   WHERE ((MAIN.ID = BATCH.ID)
     AND (BATCH.SizeTypeID = SIZETYPE.SizeTypeID)
     AND (BATCH.LocationID = LOCATES.LocationID))
     AND (((BATCH.Expiration < "DATEADD (day, 30, GETDATE()) ")))
ORDER BY MAIN.NameRaw ASC,
         LOCATES.Location ASC,
         MAIN.NameSorted ASC

It did the same type of thing with the first one.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Arthur_WoodCommented:
this:

AND (((BATCH.Expiration < "DATEADD (day, 30, GETDATE()) ")))
ORDER BY MAIN.NameRaw ASC,
         LOCATES.Location ASC,
         MAIN.NameSorted ASC


should be :

AND (((BATCH.Expiration < DATEADD (day, 30, GETDATE()) )))
ORDER BY MAIN.NameRaw ASC,
         LOCATES.Location ASC,
         MAIN.NameSorted ASC

no " s in the expression

AW
browneye9000Author Commented:
I removed the "s and I get the error.

Invalid use of keyword.  Token day,. Line Number: 19.

I wonder if the report wizard can handle this kind of code.  BTW it is used to qry a paradox db.
browneye9000Author Commented:
I decided to use access to report.  Thanks for the help though.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.