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() + "%')";

For what database? What is the data type for the UPDATE_COMPANY_CREDIT.update_date column? LIKE is for strings, which doesn't make much sense for a date... — OMG Ponies
– OMG Ponies, Commented Aug 18, 2010 at 0:45
the update_date type is datetime so I think string type is wrong i try int and it worked good — kartal
– kartal, Commented Aug 18, 2010 at 0:53
does SO have a "how to ask a meaningful question" page? could be a good time to link it. — tenfour
– tenfour, Commented Aug 18, 2010 at 0:54

user359040 · Accepted Answer · 2010-08-18 11:37:23Z

2

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)";

answered Aug 18, 2010 at 11:37

user359040

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

Comments

warriorpostman · Accepted Answer · 2010-08-18 00:30:47Z

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.

answered Aug 18, 2010 at 0:30

warriorpostman

2,3383 gold badges16 silver badges26 bronze badges

Comments

Ferruccio · Accepted Answer · 2010-08-18 00:31:46Z

1

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

answered Aug 18, 2010 at 0:31

Ferruccio

101k38 gold badges232 silver badges305 bronze badges

1 Comment

Rob Over a year ago

SQL Server 2005 will actually let you use LIKE on datetime fields, but also on int fields and even bit fields.

Azhar · Accepted Answer · 2010-08-18 04:10:16Z

0

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

answered Aug 18, 2010 at 4:10

Azhar

20.7k39 gold badges149 silver badges211 bronze badges

Collectives™ on Stack Overflow

sql "LIKE" Query syntax

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related