sql "LIKE" Query syntax

Question

what is the wrong in this Query ?

string command_get_pay = "select Credit 
                          from Update_Company_Credit 
                          where (Update_Date LIKE '%" +
    System.DateTime.Today.ToShortDateString() + "%')";

Answer 1

Some more details would be helpful. Are you expecting to see results from this query, but when it runs you get nothing? If so, chances are you need to make sure that the filter value (ie System.DateTime.Today.ToShortDateString() ) is formatted in a way that is acceptable to the T-SQL engine. For example, ToShortDateString() might return a string value that is not in a compatible format for the SQL query.

This may also depend on which relational database you are using.

Answer 2

I believe the SQL LIKE operator only works on text fields. If Update_Date is a date field, that could be a problem.

Answer 3

Check the date format in SQl Database and the one you assign though System.DateTime.Today.ToShortDateString()

Answer 4

I believe that you are trying to check that Update_date is the current date, regardless of time, and that your problem is that you are not receiving any results even though there are some Update_date values for the current date.

This is because System.DateTime.Today.ToShortDateString() converts the system date into a different format than the format used by the implicit conversion of a datetime into a string produced by the LIKE comparison.

Given that SQLServer has its own date comparison functions, I recommend that you use those, like so:

string command_get_pay = "select Credit 
                      from Update_Company_Credit 
                      where (datediff(d,Update_Date, getdate())=0)";