在 SQL 查询中以字符串格式传递日期时间？

发布于 2024-11-08 01:56:15 字数 645 浏览 0 评论 0原文

我有字符串查询并将 @Date 对象传递给字符串。它给出了错误。请参阅下面的代码。

Declare @MidDate datetime, @MaxDate datetime


set @qrysales_trans_unit_26wks ='update historical_result
    set sales_trans_unit_26wks = (      
            SELECT      
            SUM(sales_trans_unit)
            FROM reg_summary_rowno  WHERE  
            period_idx >= '+  @MidDate  // error 
            +' AND period_idx <'+  @MaxDate /error
            +' AND Client_id ='+ @Client_id
            +' and historical_result.[store_idx] = reg_summary_rowno.[store_idx]
            And [attributes] ='+  @attributes +')'

如何以正确的方式将 Datetime 对象传递给字符串查询？

原文

I have string query in and pass @Date object to string. It is giving error. See below code.

Declare @MidDate datetime, @MaxDate datetime


set @qrysales_trans_unit_26wks ='update historical_result
    set sales_trans_unit_26wks = (      
            SELECT      
            SUM(sales_trans_unit)
            FROM reg_summary_rowno  WHERE  
            period_idx >= '+  @MidDate  // error 
            +' AND period_idx <'+  @MaxDate /error
            +' AND Client_id ='+ @Client_id
            +' and historical_result.[store_idx] = reg_summary_rowno.[store_idx]
            And [attributes] ='+  @attributes +')'

How to pass Datetime object in the proper way to string Query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

掌心的温暖 2024-11-15 01:56:15

尝试使用两个单引号来转义引号，以便日期最终类似于： period_idx >= '@MidDate'

set @qrysales_trans_unit_26wks ='update historical_result
        set sales_trans_unit_26wks = (      
                SELECT      
                SUM(sales_trans_unit)
                FROM reg_summary_rowno  WHERE  
                period_idx >= '''+  @MidDate
                +''' AND period_idx <'''+  @MaxDate
                +''' AND Client_id ='+ @Client_id
                +' and historical_result.[store_idx] = reg_summary_rowno.[store_idx]
                And [attributes] ='+  @attributes +')'

单击此处。

Try using two single quotes to escape quote marks so dates end up like: period_idx >= '@MidDate'

set @qrysales_trans_unit_26wks ='update historical_result
        set sales_trans_unit_26wks = (      
                SELECT      
                SUM(sales_trans_unit)
                FROM reg_summary_rowno  WHERE  
                period_idx >= '''+  @MidDate
                +''' AND period_idx <'''+  @MaxDate
                +''' AND Client_id ='+ @Client_id
                +' and historical_result.[store_idx] = reg_summary_rowno.[store_idx]
                And [attributes] ='+  @attributes +')'

Click here for more information on escaping quotes in SQL.

回复收藏 0 原文

海拔太高太耀眼 2024-11-15 01:56:15

恕我直言，有几个更好的选择。

如果您确实想使用动态 SQL，请阅读 sp_executesql - 并使用将参数传递给 SQL 的功能。您可以通过这种方式防止 SQL 注入攻击，并且还可以避免遇到必须对参数值进行字符串化的问题。

否则，使用存储过程 - 我认为这是更好的选择。

回复收藏 0 原文

梦魇绽荼蘼 2024-11-15 01:56:15

要修复错误，您需要在字符串内的日期周围添加一些单引号 '。

还有一件事可以提高清晰度。使用 BETWEEN 关键字：

    WHERE period_idx BETWEEN @MinimumDate AND @MaximumDate

To fix your ERROR, you need to add some single quotes ' around the dates within the string.

One more thing which improves clarity. Use the BETWEEN keyword:

    WHERE period_idx BETWEEN @MinimumDate AND @MaximumDate

回复收藏 0 原文

伪装你 2024-11-15 01:56:15

您可以使用smalldatetime代替datetime
您可以使用这样的日期：

Declare @MidDate smalldatetime,
set @MidDate = '20110317'

希望它有帮助。

You can use instead of datetime a smalldatetime
And you may use the dates like this :

Declare @MidDate smalldatetime,
set @MidDate = '20110317'

Hope it helps.

回复收藏 0 原文

夏末 2024-11-15 01:56:15

如果您必须以字符串格式传递日期 - 首先，将其放在引号中，其次，我强烈建议您使用标准 ISO-8601 日期格式（YYYYMMDD 或 YYYY-MM-DDTHH:MM:SS)。

这些 ISO 标准格式的一大好处是，无论 SQL Server 设置为何种语言和区域设置，它们都可以工作。任何其他字符串表示形式均取决于语言，例如

05/10/2010

表示为

，在美国
2010 年 5 月 10 日，在世界其他地区表示为 2010 年 10 月 5 日

，但 20101005 是清晰且绝不含糊的 - 它是始终是 2010 年 10 月 5 日 - 即使在美国:-)

If you must pass a date in string format - first of all, put it in quotes, and second of all, I would strongly urge you to use the standard ISO-8601 date format (YYYYMMDD or YYYY-MM-DDTHH:MM:SS).

The big benefit of these ISO standard formats is that they'll work no matter what language and regional settings your SQL Server is set to. Any other string representation is language-dependent, e.g.

05/10/2010

will mean:

10th of May 2010 in the US
5th of October 2010 in pretty much all of the rest of the world

but 20101005 is clear and never ambiguous - it's always the 5th of October, 2010 - even for in the US :-)

回复收藏 0 原文

分开我的手 2024-11-15 01:56:15

我认为你应该在将日期变量与句子连接之前使用转换

Declare @MidDate datetime, @MaxDate datetime
set @qrysales_trans_unit_26wks = 'update historical_result
     set sales_trans_unit_26wks = (      
            SELECT      
            SUM(sales_trans_unit)
            FROM reg_summary_rowno  
            WHERE  
            period_idx >= '+ '''' + convert(varchar, @MidDate, 112) + ''''  // error 
            +' AND period_idx <'+  '''' + convert(varchar, @MaxDate, 112) + '''' /error
            +' AND Client_id ='+ @Client_id
            +' and historical_result.[store_idx] = reg_summary_rowno.[store_idx]
            And [attributes] ='+  @attributes +')'

I think you should use convert before concatenate the date variable with the sentence

Declare @MidDate datetime, @MaxDate datetime
set @qrysales_trans_unit_26wks = 'update historical_result
     set sales_trans_unit_26wks = (      
            SELECT      
            SUM(sales_trans_unit)
            FROM reg_summary_rowno  
            WHERE  
            period_idx >= '+ '''' + convert(varchar, @MidDate, 112) + ''''  // error 
            +' AND period_idx <'+  '''' + convert(varchar, @MaxDate, 112) + '''' /error
            +' AND Client_id ='+ @Client_id
            +' and historical_result.[store_idx] = reg_summary_rowno.[store_idx]
            And [attributes] ='+  @attributes +')'

回复收藏 0 原文

又怨 2024-11-15 01:56:15

我强烈建议您不要以这种方式连接 SQL。它确实会让您面临注入攻击等。

查看此示例以了解您可能采取的另一种方法。

use tempdb
create table foo (id int not null identity, data datetime)

insert foo(data) values
('1/1/2010'),('1/10/2010'),('3/31/2010')


Declare @SQLStr nvarchar(4000)

set @SQLStr = 'select * from foo where data = @Data'

exec sp_executeSQL @SQLStr, N'@Data datetime', '1/1/2010'

I would really recommend that you shy from concatenating SQL this way. It will really open you to injection attacks etc.

Look at this sample to see another approach that you might take.

use tempdb
create table foo (id int not null identity, data datetime)

insert foo(data) values
('1/1/2010'),('1/10/2010'),('3/31/2010')


Declare @SQLStr nvarchar(4000)

set @SQLStr = 'select * from foo where data = @Data'

exec sp_executeSQL @SQLStr, N'@Data datetime', '1/1/2010'

回复收藏 0 原文

~没有更多了~