CODEWITHSUNDEEP
c#sql-serverlinqasp.net-web-api
Lawraoke
Lawraoke

Reputation: 556

C# SQL statement Date Range

I would like to use SQL statement to "filter" or display the data that lies within date range.

Here is an example:

SELECT PRODUCTNAME, PRODUCTID, DESCRIPTION, EXPIREDDATE 
FROM PRODUCT TABLE 
WHERE EXPIREDDATE < (SELECT GETDATE())

The above query is able to get ExpiredDate of an product for example.

Now I am trying to let user know the following month (which will be inputted by themselves) list of product that are going to expired.

Let me elaborate: if user enters '3', then starting from today let's say 2020-01-01, until the next 3 months which will be 2020-04-01, while the ExpiredDate falls within that daterange, the list of product will be shown back to the user

public object expiredProduct (int month) //month will be user inputted value 
{
    // I skipped database connection here
    SqlCommand command = new SqlCommand();

    // what should I write here for comparing the date?
    command.CommandText = SELECT PRODUCTNAME, PRODUCTID, DESCRIPTION, EXPIREDDATE FROM PRODUCT TABLE WHERE EXPIREDDATE < (SELECT GETDATE())  

    return list; // I return back my data here
}

How should I write for the above query?

Requirement

My date is DateTime datatype. I am using C# Web API.

Upvotes: 1

Views: 1386

Answers (1)

marc_s
marc_s

Reputation: 754388

Use parameters to define the upper and lower bound of your date range - something like this:

public object expiredProduct (int month) //month will be user inputted value 
{
    // I skipped database connection here
    SqlCommand command = new SqlCommand();

    // what should I write here for comparing the date?
    command.CommandText = @"SELECT PRODUCTNAME, PRODUCTID, DESCRIPTION, EXPIREDDATE 
                            FROM PRODUCT TABLE 
                            WHERE EXPIREDDATE BETWEEN @FromDate AND @ToDate;";

    // then set these values
    command.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = DateTime.Today;
    command.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = DateTime.Today.AddMonths(month);

    return list; // I return back my data here
}

Upvotes: 4

Related Questions