browneye9000
asked on
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.
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.
Use GETDATE()+30
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
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
ASKER
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.
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.
ASKER
I decided to use access to report. Thanks for the help though.