在DB中插入DateTime中的问题 [英] Issue in Insert DateTime in DB

问题描述

我想将C＃中的DateTime插入到SQL Server DB 。我在C＃中测试之前创建了示例查询。但是发生了错误。



请有人帮忙...................以下查询中的错误是什么？ ?????

  DECLARE   @ ActivityDate   as   date  
  DECLARE   @ SqlQuery   as   varchar （ 1000 ）
 
  SET  @ ActivityDate = current_timestamp 
  SET  @ SqlQuery = ' 插入TestDB（ ActivityDate）VALUES（' + CAST（ @ ActivityDate   as   datetime ）+ ' ）' 
  Exec （ @ SqlQuery ）

错误：

从字符串转换为日期时间时出错



提前致谢

解决方案

这是另一个解决方案，

使用sp_executesql比使用exec更好，因为存储执行计划并避免语法错误以获得干净优雅的代码

  DECLARE   @ActivationDate   as  日期; 
  DECLARE   @ SqlQuery   as   nvarchar （ 1000 ）
  DECLARE   @ ParmDefinition   nvarchar （ 500 ）; 
 
  SET  @ ActivityDate = current_timestamp; 
  SET   @ ParmDefinition  = N '  @ ActivityDate datetime'; 
  SET  @SqlQuery = ' 插入TestDB （ActivityDate）VALUES（@ActivityDate）' 
 
 执行 sp_executesql  @ SqlQuery ， @ ParmDefinition ， @ ActivityDate  =  @ ActivityDate  

希望有所帮助，

Bechir。

你不能将日期时间值与字符串连接起来。



问题在于行

  SET  @ SqlQuery = ' 插入TestDB（ActivityDate）VALUES（' + CAST（ @ ActivityDate   as   datetime ）+ ' ）' 

检查以下内容 -

  DECLARE   @ ActivityDate   as   date  
  DECLARE   @ SqlQuery   as   varchar （ 1000 ）
 
  SET  @ActivationDate = current_timestamp 
  SET  @SqlQuery = ' 插入TestDB（ActivityDate）VALUES（''' + CAST （CAST（ @ ActivityDate   as   datetime ）  AS   VARCHAR ）+ ' '''' 
  Exec （ @SqlQuery ）

请注意，我在铸造后的值之前和之后还添加了2个单引号它是varchar值。



我希望这对你有帮助:)



更新1：

要获得完整的日期，需要将@ActivityDate的数据类型更改为DATETIME，如下所示

  DECLARE   @ActivationDate   as   DATETIME  
  DECLARE   @ SqlQuery   as   varchar （ 1000 ）
 
  SET  @ ActivityDate = current_timestamp 
  SET  @ SqlQuery = ' 插入TestDB（ActivityDate）VALUES（''' + CAST（ @ ActivityDate   AS   VARCHAR  ）+ ' '''' 
  Exec （ @ SqlQuery ）

更新2 ：

获得价值直到分钟，秒和&您需要使用 CONVERT 而不是 CAST 的毫秒数。请注意，代码113或13将为我们提供所需的结果。

  DECLARE   @ ActivityDate   as   DATETIME  
  DECLARE   @ SqlQuery   as   varchar （ 1000 ）
 
  SET  @ ActivityDate = current_timestamp 
  SET  @ SqlQuery = ' 插入TestDB（ ActivityDate）VALUES（''' + CONVERT（ VARCHAR ， @ ActivityDate ， 113 ）+ ' ''' 
  Exec （ @SqlQuery ）

I want to insert DateTime from C# to SQL server DB. I create example query before test in C#. but error occur.

Please anyone help................... What is error in following query???????

DECLARE @ActivityDate as date
DECLARE @SqlQuery as varchar(1000)

SET @ActivityDate=current_timestamp
SET @SqlQuery='Insert into TestDB (ActivityDate) VALUES ('+CAST(@ActivityDate as datetime)+')'
Exec(@SqlQuery)

Error:
Error during conversion from character string to datetime

Thanks in advance

解决方案

Hi,

this is another solution,
Using sp_executesql is better then using exec, because store the execution plan and avoid syntax error to have clean and elegant code

DECLARE @ActivityDate as date;
DECLARE @SqlQuery as nvarchar(1000)
DECLARE @ParmDefinition nvarchar(500);

SET @ActivityDate=current_timestamp;
SET @ParmDefinition = N'@ActivityDate datetime';
SET @SqlQuery='Insert into TestDB (ActivityDate) VALUES (@ActivityDate)'

execute sp_executesql @SqlQuery, @ParmDefinition, @ActivityDate = @ActivityDate

hope that help,
Bechir.

You can't concatenate datetime value with a string.

Problem is in the line

SET @SqlQuery='Insert into TestDB (ActivityDate) VALUES ('+CAST(@ActivityDate as datetime)+')'

Check following -

DECLARE @ActivityDate as date
DECLARE @SqlQuery as varchar(1000)
 
SET @ActivityDate=current_timestamp
SET @SqlQuery='Insert into TestDB (ActivityDate) VALUES ('''+CAST(CAST(@ActivityDate as datetime) AS VARCHAR)+''')'
Exec(@SqlQuery)

Note that I have also added 2 single quotations before and after the value after casting it to varchar value.

I hope this was helpful :)

Update 1:
To get complete date with time you need to change the datatype of @ActivityDate to DATETIME as follows

DECLARE @ActivityDate as DATETIME
DECLARE @SqlQuery as varchar(1000)
 
SET @ActivityDate=current_timestamp
SET @SqlQuery='Insert into TestDB (ActivityDate) VALUES ('''+CAST(@ActivityDate AS VARCHAR)+''')'
Exec(@SqlQuery)

Update 2:
To get value till minute, second & milliseconds you need to use CONVERT instead of CAST. Note that code 113 or 13 will give us the desired result.

DECLARE @ActivityDate as DATETIME
DECLARE @SqlQuery as varchar(1000)
 
SET @ActivityDate=current_timestamp
SET @SqlQuery='Insert into TestDB (ActivityDate) VALUES ('''+CONVERT(VARCHAR,@ActivityDate,113)+''''
Exec(@SqlQuery)

这篇关于在DB中插入DateTime中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！