SQL Server时区转换函数VARCHAR转DATETIME超出范围报错求助

SQL Server时区转换函数VARCHAR转DATETIME超出范围报错求助

阿华AIGC实验室

2026-5-15

解决SQL Server中VARCHAR转DATETIME的范围溢出转换错误

咱们一步步拆解这个转换错误，找到根源并给出可行的修复方案。

问题回顾

你遇到的核心错误是：

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

这个错误触发在调用fn_US_UTC_TimeDifference函数进行时区转换的场景中，结合你提供的函数代码和错误堆栈，我们可以定位到几个关键问题点。

相关函数代码

主转换函数 `fn_US_UTC_TimeDifference`

ALTER FUNCTION [dbo].[fn_US_UTC_TimeDifference] ( 
    @date DATETIME ,
    @OneMinuteToMidnightFlag BIT 
) RETURNS VARCHAR(19) AS 
BEGIN 
    DECLARE @ChangedDate VARCHAR(19) 
        ,@StartDate DATETIME 
        ,@EndDate DATETIME 

    IF (@OneMinuteToMidnightFlag = 1) 
    BEGIN 
        IF ( DATEPART(hh, @date) = 23 AND DATEPART(mi, @date) = 59 ) 
        BEGIN 
            SET @ChangedDate = CONVERT(VARCHAR, @date, 126) 
            RETURN @ChangedDate 
        END 
    END 

    SET @StartDate = ( SELECT dbo.fn_EDT_StartDate(Year(@date)) ) 
    SET @EndDate = ( SELECT dbo.fn_EDT_EndDate(Year(@date)) ) 

    IF ( @date >= @StartDate AND @date < @EndDate ) 
    BEGIN 
        SET @ChangedDate = CONVERT(NVARCHAR, DATEADD(hh, 4, @date), 126) 
    END 
    ELSE 
    BEGIN 
        SET @ChangedDate = CONVERT(NVARCHAR, DATEADD(hh, 5, @date), 126) 
    END 

    RETURN @ChangedDate 
END

EDT起始日期函数 `fn_EDT_StartDate`

ALTER FUNCTION [dbo].[fn_EDT_StartDate](@Year INT) 
RETURNS DATETIME AS 
BEGIN 
    DECLARE @March1st DATETIME = DATEADD(YEAR, @Year-1900, '1900-03-01') 
    DECLARE @DayOfWeek INT = DATEPART(DW, @March1st) 
    DECLARE @Result DATETIME 

    IF @DayOfWeek = 1 
    BEGIN 
        SET @Result = DATEADD(D, 7, @March1st) 
    END 
    ELSE 
    BEGIN 
        SET @Result = DATEADD(D, 15-@DayOfWeek, @March1st) 
    END 

    RETURN @Result 
END

EDT结束日期函数 `fn_EDT_EndDate`

ALTER FUNCTION [dbo].[fn_EDT_EndDate](@Year INT) 
RETURNS DATETIME AS 
BEGIN 
    DECLARE @Nov1st DATETIME = DATEADD(YEAR, @Year-1900, '1900-11-01') 
    DECLARE @DayOfWeek INT = DATEPART(DW, @Nov1st) 
    DECLARE @Result DATETIME 

    IF @DayOfWeek = 1 
    BEGIN 
        SET @Result = @Nov1st 
    END 
    ELSE 
    BEGIN 
        SET @Result = DATEADD(D, 8-@DayOfWeek, @Nov1st) 
    END 

    RETURN @Result 
END

完整错误堆栈

4:36:15,160 [14080] ERROR EMIRAutoTRS.Program - System.Data.SqlClient.SqlException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. 
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) 
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) 
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) 
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) 
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() 
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) 
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command) 
at EMIRAutoTRS.EMIRDB.PreProcessCalypso() in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\EMIRDB.cs:line 104
14:36:15,937 [14080] ERROR EMIRAutoTRS.Program - ProcessEMIRData() failed with error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
14:36:15,937 [14080] ERROR EMIRAutoTRS.Program - System.Data.SqlClient.SqlException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command) at EMIRAutoTRS.EMIRDB.PreProcessCalypso() in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\EMIRDB.cs:line 119 at EMIRAutoTRS.FileProcessor.ProcessEMIRData() in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\FileProcessor.cs:line 913
14:36:15,947 [14080] ERROR EMIRAutoTRS.Program - ProcessFiles() failed with error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
14:36:15,951 [14080] ERROR EMIRAutoTRS.Program - System.Data.SqlClient.SqlException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command) at EMIRAutoTRS.EMIRDB.PreProcessCalypso() in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\EMIRDB.cs:line 119 at EMIRAutoTRS.FileProcessor.ProcessEMIRData() in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\FileProcessor.cs:line 931 at EMIRAutoTRS.FileProcessor.ProcessFiles() in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\FileProcessor.cs:line 259
14:36:15,959 [14080] ERROR EMIRAutoTRS.Program - Error in Main(): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
14:36:15,960 [14080] ERROR EMIRAutoTRS.Program - System.Data.SqlClient.SqlException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command) at EMIRAutoTRS.EMIRDB.PreProcessCalypso() in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\EMIRDB.cs:line 119 at EMIRAutoTRS.FileProcessor.ProcessEMIRData() in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\FileProcessor.cs:line 931 at EMIRAutoTRS.FileProcessor.ProcessFiles() in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\FileProcessor.cs:line 357 at EMIRAutoTRS.FileProcessor.ProcessEmir() in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\FileProcessor.cs:line 56 at EMIRAutoTRS.Program.Main(String[] args) in H:\EMIR ASIC TFS Location\EMIRASIC\Development\EMIRAutoTRS\Program.cs:line 70
14:36:15,970 [14080] INFO EMIRAutoTRS.Program - Exit code was: -1

问题根源分析

不必要的类型转换是核心诱因：
你的函数本质是做时间偏移计算，却返回VARCHAR(19)类型。这意味着后续程序必须把这个字符串再转换回DATETIME，而转换过程中如果会话的DATEFORMAT设置（比如dmy）或者程序端的区域设置与字符串格式不匹配，就会触发范围溢出错误。比如程序端用MM/dd/yyyy格式去解析yyyy-mm-dd字符串，就会出现无效日期解析。
EDT日期计算依赖不稳定的会话设置：
两个辅助函数中使用DATEPART(DW, ...)获取星期几，这个函数的返回值完全依赖数据库的SET DATEFIRST设置（默认是7，周日为第一天）。如果数据库的DATEFIRST被修改为其他值（比如1，周一为第一天），计算出的EDT起止日期就会错误，进而导致DATEADD后生成无效的DATETIME值，最终在转换字符串时触发错误。
日期构造方式不够安全：
辅助函数中用DATEADD(YEAR, @Year-1900, '1900-03-01')构造日期，虽然能工作，但不如DATEFROMPARTS(@Year, 3, 1)直观且安全，后者专门用于构造日期，不会有隐式转换的风险。

针对性解决方案

方案1：修改主函数返回类型为DATETIME（最推荐）

直接返回DATETIME类型，彻底消除字符串转换环节，从根源避免错误：

ALTER FUNCTION [dbo].[fn_US_UTC_TimeDifference] ( 
    @date DATETIME ,
    @OneMinuteToMidnightFlag BIT 
) RETURNS DATETIME AS 
BEGIN 
    -- 如果是23:59且标记开启，直接返回原日期
    IF (@OneMinuteToMidnightFlag = 1 
        AND DATEPART(HOUR, @date) = 23 
        AND DATEPART(MINUTE, @date) = 59)
    BEGIN 
        RETURN @date 
    END 

    DECLARE @StartDate DATETIME = dbo.fn_EDT_StartDate(YEAR(@date)) 
    DECLARE @EndDate DATETIME = dbo.fn_EDT_EndDate(YEAR(@date)) 

    -- 根据时区返回偏移后的日期
    RETURN CASE 
        WHEN @date >= @StartDate AND @date < @EndDate THEN DATEADD(HOUR, 4, @date)
        ELSE DATEADD(HOUR, 5, @date)
    END
END

方案2：修复EDT日期计算的会话依赖问题

修改两个辅助函数，让它们的星期计算不依赖DATEFIRST设置，同时使用更安全的日期构造方式：

-- 修复EDT起始日期函数（3月的第二个周日）
ALTER FUNCTION [dbo].[fn_EDT_StartDate](@Year INT) 
RETURNS DATETIME AS 
BEGIN 
    DECLARE @March1st DATETIME = DATEFROMPARTS(@Year, 3, 1)
    -- 计算星期几，1=周日，2=周一...7=周六，不受DATEFIRST影响
    DECLARE @DayOfWeek INT = ((DATEPART(WEEKDAY, @March1st) + @@DATEFIRST - 2) % 7) + 1;

    DECLARE @Result DATETIME 
    -- 如果3月1日是周日，加7天得到第二个周日；否则计算到下一个周日再加7天
    SET @Result = DATEADD(DAY, CASE WHEN @DayOfWeek = 1 THEN 7 ELSE 15 - @DayOfWeek END, @March1st)

    RETURN @Result 
END

-- 修复EDT结束日期函数（11月的第一个周日）
ALTER FUNCTION [dbo].[fn_EDT_EndDate](@Year INT) 
RETURNS DATETIME AS 
BEGIN 
    DECLARE @Nov1st DATETIME = DATEFROMPARTS(@Year, 11, 1)
    DECLARE @DayOfWeek INT = ((DATEPART(WEEKDAY, @Nov1st) + @@DATEFIRST - 2) % 7) + 1;

    DECLARE @Result DATETIME 
    -- 如果11月1日是周日直接返回，否则计算到第一个周日
    SET @Result = DATEADD(DAY, CASE WHEN @DayOfWeek = 1 THEN 0 ELSE 8 - @DayOfWeek END, @Nov1st)

    RETURN @Result 
END

方案3：如果必须返回字符串，确保转换一致性

如果业务要求必须返回字符串，那么要确保函数返回的格式在程序端能被正确解析：

-- 修改主函数，明确指定转换长度和格式
ALTER FUNCTION [dbo].[fn_US_UTC_TimeDifference] ( 
    @date DATETIME ,
    @OneMinuteToMidnightFlag BIT 
) RETURNS VARCHAR(23) AS -- 126格式包含毫秒，长度23足够
BEGIN 
    DECLARE @ChangedDate VARCHAR(23) 
        ,@StartDate DATETIME 
        ,@EndDate DATETIME 

    IF (@OneMinuteToMidnightFlag = 1) 
    BEGIN 
        IF ( DATEPART(hh, @date) = 23 AND DATEPART(mi, @date) = 59 ) 
        BEGIN 
            SET @ChangedDate = CONVERT(VARCHAR(23), @date, 126)

火山引擎最新活动

方舟 Coding Plan

模型自由，工具不限，免费解锁 ArkClaw，7*24 小时在线的专属智能伙伴

一键部署 OpenClaw

分钟级部署，云服务器包月低至￥9.9，与 CodingPlan 组合购买仅需19.8元

Seedance2.0 体验中心上线

注册即享免费500万Tokens，抢先领略新一代AI视频技术跃迁

新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠