How To Select Items Getting Expired Within A Specific Time In SQL Server?

Question

I am using SQL Server 2008 with a C# Windows Forms application. The products table has other values along with the expiry date and I want to show ProductID, ProductName, AvailableQuantity, and ExpiryDate in a grid so I can allow users to get an idea of items which are getting expired before the due date.

The purpose is to add only those products to that grid whose expiry date is getting closer, i.e. which are getting expired in 2 months.

I have tried using the TRUNC function like as follow:

select * 
from tblProducts 
where DateExpiry < TRUNC( SYSDATE ) + INTERVAL '62' DAY;

but I am getting the following error in SQL Server 2008:

Msg 195, Level 15, State 10, Line 1
'TRUNC' is not a recognized built-in function name.

Why I am getting this error? I have seen online that the same code is working fine, however, it is generating the error in my case. Is there any other way that can fulfill my requirements? Or do I have to modify the query somehow, which can help me resolve my error?

user_2457865 · Answer

In SQL Server you should do this.

select * 
from tblProducts 
where DateExpiry < DATEADD(month, 2, GETDATE());

user_1043380 · Answer

DateDiff can be used to compute the total time frame between two dates. Those dates can come both from table values, or one can be "today's" date. I'd suggest you diff by total number of days rather than months because "a month" is a variable amount of time that changes often.

select * 
from tblProducts 
where DATEDIFF(day, GETDATE(), DateExpiry) < 62

It's assumed that DateExpiry should be greater than the current date, because we're looking for values that have not expired yet, but should soon.

Rather than trying to compute what "today plus 62 days" is, then trying to see if the database value is less than that, the command just subtracts the database value from the current date and checks if it's less than a number.

This selects anything that has will expire in less than 62 days from today.

How To Select Items Getting Expired Within A Specific Time In SQL Server?

2 Answers