Compare two dates in c# by passing sql query

Question

I have a table named RETAILTRANSACTIONTABLE which has a column named BUSINESSDATE which has date in format(yyyy-mm-dd) 2015-05-22.

I want to comapre this date with batch.StartDateTime of format (dd-mm-yyyy hh:mm:ss) 05-10-2015 12:09:03.

var dateAndTime = batch.StartDateTime;
var date = dateAndTime.ToShortDateString();
string query = "SELECT COUNT(discamount) AS DISCOUNTCOUNT "+
                " FROM ax.RETAILTRANSACTIONTABLE where "+
                "(CONVERT(VARCHAR(10),BUSINESSDATE,105) >= '" + date
                + "') and DISCAMOUNT > 0

Using the above query gives me all the values of the column I just want the count of values greater than or equal to batch date

If it's a date datatype, the representation as written is not important. It's a date (just make the question unclear). — Khonsort, Oct 09 '15 at 06:19
Possible duplicate of [MS SQL compare dates?](http://stackoverflow.com/questions/4794028/ms-sql-compare-dates) — Ken Y-N, Oct 09 '15 at 06:20
@GajendraRajput May be `var date = dateAndTime.ToShortDateString("yyyy-MM-dd");` — Thirisangu Ramanathan, Oct 09 '15 at 06:21
For parameterising queries, [see this question and answer](https://stackoverflow.com/questions/7505808/using-parameters-in-sql-statements). — Ken Y-N, Oct 09 '15 at 06:21

score 0 · Answer 1 · answered Oct 09 '15 at 06:27

You can use :

var dateAndTime = batch.StartDateTime;
var date = dateAndTime.ToShortDateString();
string query = "SELECT COUNT(discamount) AS DISCOUNTCOUNT "+
            " FROM ax.RETAILTRANSACTIONTABLE "+
            " WHERE BUSINESSDATE >= CONVERT(DATE, '" + date + "', 105) 
            and DISCAMOUNT > 0 "

And, bonus, this use index on BUSINESSDATE if an index exist.

or better as @FelixPamittan said, use a prepared statement with binding :

var dateAndTime = batch.StartDateTime;
var date = dateAndTime.ToShortDateString();
string query = "SELECT COUNT(discamount) AS DISCOUNTCOUNT "+
            " FROM ax.RETAILTRANSACTIONTABLE "+
            " WHERE BUSINESSDATE >= :theDate 
            and DISCAMOUNT > 0 "

score 0 · Answer 2 · answered Oct 09 '15 at 06:34

I think this will do the trick for you

var dateAndTime = batch.StartDateTime;
var date = dateAndTime.ToShortDateString();
string query = "SELECT COUNT(discamount) AS DISCOUNTCOUNT " +
                " FROM ax.RETAILTRANSACTIONTABLE WHERE" +
                "BUSINESSDATE > = (CONVERT(VARCHAR, " + @dateParam + " ,105) >= '" + 
                + "') and DISCAMOUNT > 0

SqlConnection connection = new SqlConnection(/* connection info */);
SqlCommand command = new SqlCommand(query, connection);

command.Parameters.AddWithValue("dateParam", date.ToString("yyyy/mm/dd"));

score 0 · Answer 3 · answered Oct 09 '15 at 09:22

0

With the help of above answers I modified it and finally got the solution

var dateAndTime = batch.StartDateTime;
var date = dateAndTime.ToString("yyyy-MM-dd");
string query = "SELECT COUNT(discamount) AS DISCOUNTCOUNT "+
               " FROM ax.RETAILTRANSACTIONTABLE where "+                    
               "BUSINESSDATE >= '" + date + "'" + 
               " and DISCAMOUNT > 0

answered Oct 09 '15 at 09:22

Gajendra Rajput

93
1
13

2

It's better to convert explicitaly with the format. use `CONVERT(DATE, '"+date+"', 120)` instead of `'"+date+"'` – Khonsort Oct 09 '15 at 11:41
But preparedStatement is the best ;-) – Khonsort Oct 09 '15 at 11:41

Compare two dates in c# by passing sql query

3 Answers3