Click here to Skip to main content
14,699,107 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Some help would be appreciated.
I have a table tblItem with columns ExpireDate (DateTime), AlertDays (Int).
Each Item I insert has an expiredate, I want to be alert in x days before the expiredate.
I want to count all the items where today + alertdays <= ExpireDate.
I have the following code but it is causing an error:
There was an error parsing the query. [ Token line number = 1,Token line offset = 48,Token in error = select ]


internal static int getAlertDateExpire()
{
    SqlCeConnection con = ConectionAndData.Con;

    if (con.State == System.Data.ConnectionState.Closed)
        con.Open();

    int number = 0;

    string sqlCommand = "select count(*) from tblItem where (Getdate()+(select AlertDays from tblItem)) As today <= ExpireDate";

    SqlCeCommand com = new SqlCeCommand(sqlCommand, con);

    try
    {
        number = (Int32)com.ExecuteScalar();
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
    }

    return number;
}


What I have tried:

string sqlCommand = "(select count(*) from tblItem where DATEADD(day, (select AlertDays from tblItem) ,Getdate()) <= ExpireDate)";
Posted
Updated 18-Mar-16 9:01am

1 solution

From the code posted, I assume you are trying to get the count of records having ExpireDate less than or equal to current date plus number of alertdays. If that is the case, modify the query to something like below and it should work.
select count(*) from tblitem where dateadd(day, AlertDays,getdate()) <= ExpireDate
   
Comments
plazzasele 18-Mar-16 18:57pm
   
@Mathi Mani. Thank you for your reply. I have one record with production date: 19.03.2016 00:00:00:00 And ExpireDate 22.03.2016 00:00:00:00. and alert me before expire date in 4 days. when i use your suggestion: (select count(*) from tblitem where dateadd(day, AlertDays,getdate()) <= ExpireDate) I get 0. if i change <= to >= I get one record. I thing your suggestion is right, maybe I am wrong when i set the condition <=. I need help to understand this. thanks again.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900