How can I add this sign %% into this code

Question

1.00/5 (1 vote)

See more:

Hello everyone I am planning to create a per day backup of specific table in my database inside the database I have a table named itemsreceived and column name DateCreated the DataTypes is datetime and it has a value of (2021-01-07 08:23:26).
I what to get all the data of 2021-01-07. I have this line of code inside the form

"Select * From itemsreceived" +
"WHERE date_format(DateCreated, '%Y-%m-%d') " +
 "LIKE date_format('" + datepicker1.ToString("yyyy-MM-dd") + "', '%Y-%m-%d')" + ";";

to transform it to sql statement it looks like this
Select * from itemsreceived where DateCreated Like '2021-01-07';

But when I run this code there is no result.
but when I tried adding %% that look like this
Select * from itemsreceived where DateCreated Like '%2021-01-07%';
the result came out

The Question is
How can I add this sign %% to look like
Select * from itemsreceived where DateCreated Like '%%';

from this line of code

"Select * From itemsreceived" +
"WHERE date_format(DateCreated, '%Y-%m-%d') " +
 "LIKE date_format('" + datepicker1.ToString("yyyy-MM-dd") + "', '%Y-%m-%d')" + ";";

What I have tried:

I dont know how to do this right, I tried but no ones is working
"LIKE date_format('" + '%'+ date.ToString("yyyy-MM-dd") +  '%' + "', '%Y-%m-%d')" + ";";

"LIKE date_format('%" + date.ToString("yyyy-MM-dd") + "%', '%Y-%m-%d')" + ";";

Posted 11-Jan-22 18:35pm

Member 13890537

Updated 11-Jan-22 22:41pm

Add a Solution

Comments

Richard MacCutchan 12-Jan-22 3:56am

Convert the date to its string representation before you pass it into the SELECT string.

Chris Copeland 12-Jan-22 4:59am

Just out of curiosity, why are you doing a LIKE on two dates which have been formatted to match specific strings? If both the DateCreated and the datepicker1.ToString("yyyy-MM-dd") are both formatted to '%Y-%m-%d' then the LIKE and the % symbols won't do anything.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

M Imran Ansari · Answer 1 · 2022-01-11T19:14:00

Solution 1

Just try the following code to get your result.

"SELECT * FROM itemsreceived WHERE DateCreated LIKE '%" + datepicker1.ToString("yyyy-MM-dd") + "%';";

Posted 11-Jan-22 19:14pm

M Imran Ansari

Comments

Member 13890537 12-Jan-22 1:29am

Is it imposible to add this sign %% in this code?
"Select * From itemsreceived" +
"WHERE date_format(DateCreated, '%Y-%m-%d') " +
"LIKE date_format('" + datepicker1.ToString("yyyy-MM-dd") + "', '%Y-%m-%d')" + ";";

M Imran Ansari 12-Jan-22 1:39am

Yes. If you are looking to add %% with datepicker1, here we go

"Select * From itemsreceived " +
"WHERE date_format(DateCreated, '%Y-%m-%d') " +
"LIKE date_format('%" + datepicker1.ToString("yyyy-MM-dd") + "%', '%Y-%m-%d')" + ";";

Member 13890537 12-Jan-22 1:47am

thanks but I already tried this in the section of what I have tried
"LIKE date_format('%" + datepicker1.ToString("yyyy-MM-dd") + "%', '%Y-%m-%d')" + ";";

but this is not working

M Imran Ansari 12-Jan-22 4:14am

What error you are getting on this string?

_Asif_ · Answer 2 · 2022-01-11T22:41:00

Your query should look like this, you don't need to use Like operator at all. THe logic is:

Get all from itemsreceived where DateCreated greater then and equal to 2021-01-07 and less then 2021-01-08

SQL

SELECT * FROM itemsreceived 
WHERE DateCreated >= CONVERT(datetime, datepicker1.ToString("yyyy-MM-dd")), 
AND DateCreated < DateAdd(dd, 1, CONVERT(datetime, datepicker1.ToString("yyyy-MM-dd")))