C# SQL statement Date Range

Question

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.

You'll want to add parameters to command.Parameters and reference them in the query. — ProgrammingLlama
– ProgrammingLlama, Commented Jun 11, 2020 at 3:58
@John do you mean that I need to create 2 parameters, which store today date SELECT GETDATE() and another date respectively? But now I stuck at how to add the month based on user input to today date — Lawraoke
– Lawraoke, Commented Jun 11, 2020 at 4:02

Sir Rufo · Accepted Answer · 2020-06-11 04:14:35Z

4

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
}

edited Jun 11, 2020 at 4:14

Sir Rufo

19.3k3 gold badges47 silver badges79 bronze badges

answered Jun 11, 2020 at 4:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lawraoke Over a year ago

Thanks @marc_s! For additional information, may I know that if we could just add the month directly using C# code (not via Linq)?

marc_s Over a year ago

@Lawraoke: that's not LINQ - that's standard C# / Base Class Library code

Collectives™ on Stack Overflow

C# SQL statement Date Range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related