Expiration Date

[Former Member]

I'm trying to create a query that automaticaly tells me which products have only 30 days left before they expire so I can create an alarm that runs automaticaly every day.

Here's my problem, I can't find a way to compare the expiration date [ExpDate] with the current date because the current date is in a datetime format, thus it can't be compared to a date only field...

I've been trying to convert the "[date]" using several SQL comands within the SBO query creator, but without any luck, I always get the same error, cannot convert datetime (or something like that)

I think there must be someone out there that "manages" products with expiration dates, so there must be someone that has already faced this problem before...

I basicaly want to create a simple query, something like:

Select

ItemCode, BathchNum, ExpDate

Where

Expdate <= "[date + 30]"

I left the Tables out because I'm having a problem with them as well, I haven't quite figured out which ones the data has to come from, I guess OIBT would be the main one, but I'm not sure

Could Someone help me??? (please)

[Former Member · Accepted solution]

The right syntax for your query is:

DateDiff(DD,ExpDate,GetDate()) <= 30

Thanks,

Gordon

[Former Member · Accepted solution]

Gordon

You Managed to solve a problem that the company/consultants that we used to implement SBO couldn't solve in several weeks.

It was just a matter of changing the order in the DateDiff, Instead of DateDiff(DD,ExpDate,GetDate()) <= 30 it was DateDiff(DD,GetDate(),ExpDate <=30)

You have no idea how long I've been trying to solve this, knowing that the solution was going to be real simple, THANKS

Hugo