在C#中使用datetimepicker查询SQL [英] Query SQL using datetimepicker in C#

问题描述

我正在尝试使用C#和dateTimePicker查询SQL Server.但是我的SQL Server在date_time行中使用VARCHAR类型.如何将日期时间转换为VARCHAR?

SQL中的行格式为VARCHAR，例如:20171106101700(年/月/日/小时/分钟/秒)

C#中的SQL代码是:

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

谢谢！

我尝试过的事情:

使用
进行转换
Convert(varchar(8)，BIRTH_TIME，112)

Thank you!

What I have tried:

convert using

Convert(varchar(8), BIRTH_TIME, 112)

推荐答案

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

请参阅注释中的建议部分
试试这个代码

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

注意:格式化sql查询字符串的格式为易受攻击到 ^ ]攻击
始终使用参数化查询来防止SQL注入SQL Server中的攻击 [ ^ ]

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

这篇关于在C#中使用datetimepicker查询SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！