代码之家 › 专栏 › 技术社区 › hud

执行存储过程会导致错误

sql-server-2005 stored-procedures sql-server sql

hud · 技术社区 · 8 年前

我有一个存储过程,我想在其中获取 reportdate 执行时。

我将一个参数传递给存储过程以执行它,。我就这样通过了

exec UserReportData '10-06-2016'

但我得到一个错误:

将char数据类型转换为datetime数据类型导致datetime值超出范围。

这是我的存储过程:

ALTER PROCEDURE [dbo].[UserReportData] 
    @As_ONDATE Datetime 
AS 
BEGIN 
    DECLARE @REPORTDATE datetime        
    --DECLARE @OPENING INT      

    SELECT * 
    INTO #temptable
    FROM
        (SELECT 
             a.CUser_id, b.User_Id, a.U_datetime AS REPORTDATE
         FROM 
             inward_doc_tracking_trl a, user_mst b
         WHERE
             a.CUser_id = b.mkey
             AND CONVERT(varchar(50), a.U_datetime, 103) = @As_ONDATE) AS x

    DECLARE Cur_1 CURSOR FOR 
        SELECT CUser_id, User_Id 
        FROM #temptable

    OPEN Cur_1

    DECLARE @CUser_id INT
    DECLARE @User_Id INT

    FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SELECT CONVERT(varchar(50), U_datetime, 103) 
        FROM inward_doc_tracking_trl                        
        WHERE CONVERT(varchar(50), U_datetime, 103) = @As_ONDATE

        UPDATE #temptable
        SET REPORTDATE = @REPORTDATE
        WHERE CUser_id = @CUser_id
          AND User_Id = @User_Id

        FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id
    END

    CLOSE Cur_1
    DEALLOCATE Cur_1

    SELECT * FROM #temptable

    DROP TABLE #temptable                           
END

请帮我知道错误的原因。

2 回复 | 直到 8 年前

marc_s 8 年前

各种设置(语言、日期格式)仅影响 DateTime 在SQL Server Management Studio中向您显示,或者在尝试将字符串转换为 日期时间 .

SQL Server支持多种格式-请参阅 MSDN Books Online on CAST and CONVERT 。大多数格式是 依靠的 因此,这些设置有时可能有效,有时则无效。

解决此问题的方法是使用(稍作修改) ISO-8601日期格式 SQL Server支持的-此格式有效总是 -无论您的SQL Server语言和日期格式设置如何。

这个 ISO-8601 format SQL Server支持两种类型:

YYYYMMDD 仅适用于日期(无时间部分);此处请注意: 没有破折号! ,这很重要! YYYY-MM-DD 是不是独立于SQL Server中的日期格式设置不是在任何情况下都要工作!

或:

YYYY-MM-DDTHH:MM:SS 日期和时间-此处注意:此格式有破折号(但它们可以省略),并且固定 T 作为日期和时间部分之间的分隔符 DATETIME .

这对SQL Server 2000及更新版本有效。

如果您使用SQL Server 2008或更新版本,并且 DATE 数据类型(仅限 日期 - 不 日期 !), 那么您确实也可以使用 年-月-日 格式,这也适用于SQL Server中的任何设置。

不要问我为什么整个话题如此棘手和令人困惑——这就是问题所在 年/月/日 格式,您应该可以使用任何版本的SQL Server以及SQL Server中的任何语言和日期格式设置。

对于SQL Server 2008及更新版本,建议使用 日期 如果您只需要日期部分,并且 DATETIME2(n) 当你需要日期和时间的时候。你应该开始逐步淘汰 日期 数据类型(如果可能)

因此,在具体情况下,只需将存储过程的调用方式更改为:

exec UserReportData '20160610'    -- 10th of June, 2016

或

exec UserReportData '20161006'    -- 6th of October, 2016

取决于您感兴趣的是2016年10月6日还是6月10日。。。

Ajay2707 calm 8 年前

你正在通过 datetime ,默认情况下,SQL Server使用 MMDDYYYY 或 YYYYMMDD 总体安排

您正在使用 DD-MM-YYYY 总体安排

要么你写为

set dateformat dmy --set ddmmyyyy format
exec UserReportData '10-06-2016'
set dateformat mdy  --set default again

或者您传递了格式正确的值。没有其他解决方案。