Click here to Skip to main content
13,558,603 members
Rate this:
 
Please Sign up or sign in to vote.
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:

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
Updated 6-Nov-17 19:46pm
v2
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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)+'%'
  Permalink  
v3
Comments
Claude Hitech 7-Nov-17 5:28am
   
Hello dear...I tried to use this but there is an error:

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Invalid column name 'dateTimePicker'.
Santosh kumar Pithani 8-Nov-17 4:01am
   
if "dateTimePicker" is invalid column of you then You have to pass valid "DateTIME" column Name or Date Parameter(@DateTime) instead of this "dateTimePicker".
I have updated query with example check it and let me know
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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[^]
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web04-2016 | 2.8.180515.1 | Last Updated 7 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100