Query SQL using datetimepicker in C#

Question

0.00/5 (No votes)

See more:

I am trying to query SQL Server using C# and dateTimePicker. But my SQL Server is using VARCHAR type in date_time row. How can I convert the datetime to VARCHAR?

The row in SQL is in format VARCHAR like this: 20171106101700 (Year/Month/Day/Hour/Min/Sec)

And the SQL code in c# is:

C#

comando.CommandText = "Select ID, BIRTH_TIME, NAME, ADDRESS, PHONE, COUNTRY from USERDB where BIRTH_TIME Like "' + this.dateTimePicker.Value.ToString("yyyyMMdd") + "'";

Thank you!

What I have tried:

convert using

Convert(varchar(8), BIRTH_TIME, 112)

Posted 6-Nov-17 12:19pm

Claude Hitech

Updated 6-Nov-17 19:46pm

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 6-Nov-17 19:57pm

First, fix the database; always store dates as datetimes, never as strings.
Second, use a parameterized statement, never use concatenation to form them.

GKP1992 6-Nov-17 22:40pm

As PIEBALDconsult pointed out, storing dates as strings in the database is a horrible idea. I can think of only one reason why someone would have done so, is to be able to use the like clause etc, but even then it does have any advantages. It just slows down your DB server. Please use DateTime.

Claude Hitech 7-Nov-17 5:22am

I cannot change any SQL parameters, because I am not the admin.

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)

Santosh kumar Pithani · Answer 1 · 2017-11-06T18:29:00

DECLARE @dt Datetime =(select '2017-11-06 10:17:00');

SELECT SUBSTRING(CONVERT(VARCHAR(10),DT,112),1,8) AS DT
   FROM (SELECT @dt as DT)as ff 
    where '20171106101700' 
      like 
     '%'+SUBSTRING(CONVERT(VARCHAR(10),@dt,112),1,8)+'%';

SELECT DT
   FROM (SELECT SUBSTRING(CONVERT(VARCHAR(10),@dt ,112),1,8) AS DT)as ff 
    where 
      SUBSTRING(DT,1,8)=SUBSTRING(CONVERT(VARCHAR(10),@dt,112),1,8)
----
DT
----
20171106
---------------------------------------------------------

SELECT ID,BIRTH_TIME,NAME,ADDRESS,PHONE,COUNTRY 
  FROM  USERDB
    WHERE 
     CONVERT(VARCHAR(20),BIRTH_TIME,112) 
            LIKE 
    '%'+SUBSTRING(CONVERT(VARCHAR(20),dateTimePicker,112),1,8)+'%'

Karthik_Mahalingam · Answer 2 · 2017-11-06T19:46:00

refer the advice from comments section
try this code

string value =  this.dateTimePicker.Value.ToString("yyyyMMdd");
          string query = "Select ID, BIRTH_TIME, NAME, ADDRESS, PHONE, COUNTRY from USERDB where SUBSTRING(BIRTH_TIME, 1, 8) = @birth ";
          SqlCommand cmd = new SqlCommand(query,con);
          cmd.Parameters.Add("@birth",value);

Note : Formatting the sql Query string is vulnerable to SQL Injection[^] attacks
always use Parameterized queries to prevent SQL Injection Attacks in SQL Server[^]

Query SQL using datetimepicker in C#

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0