• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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.

0
browneye9000
Asked:
browneye9000
1 Solution
 
raviberiCommented:
Use GETDATE()+30
0
 
HuyBDCommented:
Try this!

DATEADD (day, 30, GETDATE())

It return new date(today+30days)
0
 
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.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
0
 
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.
0
 
browneye9000Author Commented:
I decided to use access to report.  Thanks for the help though.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now