如何在动态查询中转换日期时间字段? [英] How to convert datetime field in dynamic query?

问题描述

首先感谢代码项目成员！！

我在SQL SERVER中遇到问题.我写了一个动态查询，其日期为where子句

因为它是动态查询，所以给我错误

从字符串转换日期时间时转换失败.

  set   @ SQLQry  =  ' 插入#tmpSpeed(Speed)SELECT FROM FROM'
    + @ tablenameSt + '  WHERE SIMID =' +  @ TrackerID  + ' 
    AND日期介于CONVERT(datetime，''' + @ StartDateTime + ' ''，120)和CONVERT( datetime，''' + @ EndDateTime + ' ''，120)' 

请说明如何解决.

:(

解决方案

将样式"指定为120时，您指定SQL应将输入字符串视为规范:即yyyy-mm-dd hh:mi :ss(24h)如果您的数据不完全相同，则会出现错误.

MSDN [
在Sql Server中，它考虑"MM/dd/yyyy"格式的日期时间. sql-server-performance.com/faq/conversion_failed_datatime_character_p1.aspx">http://www.sql-server-performance.com/faq/conversion_failed_datatime_character_p1.aspx [

Hi,

First thank you code project members!!

I am facing a problem in SQL SERVER. I wrote a dynamic query with dated where clause

as it is dynamic query it giving me error

Conversion failed when converting datetime from character string.

set @SQLQry ='Insert into #tmpSpeed(Speed) SELECT Speed FROM '
    +@tablenameSt+' WHERE SIMID ='+ @TrackerID +'
    AND dated between CONVERT(datetime,'''+@StartDateTime+ ''', 120)  and  CONVERT(datetime,'''+@EndDateTime+ ''', 120)'

Please tell how can it solved.

:(

解决方案

When you specify the "style" as 120, you specify that SQL should treat the input string as canonical: i.e. yyyy-mm-dd hh:mi:ss(24h) If your data is not exactly that, then you will get an error.

MSDN[^]

Hi,

Check it your input.
May be you can pass the datetime in ''dd/MM/yyyy'' format.

In Sql server,its consider the datetime in ''MM/dd/yyyy'' format.

Complete error detail with sample example here: http://www.sql-server-performance.com/faq/conversion_failed_datatime_character_p1.aspx[^]

Generally, SQL Server expects dates in the format mm/dd/yyyy, so you will need to convert your date into this format before submitting.

Try something like:

Dim myDatevalue as new SqlParameter("@StartDateTime", Format("myDatevalue","mm/dd/yyyy"))

这篇关于如何在动态查询中转换日期时间字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！