sql - 如何以通用方式处理来自 XML 输入的 SQL DateTime 转换

Question

我有数据以 XML 形式进入存储过程。其中一个元素是 DateTime 值。有时当我得到它具有“Z”时区名称的值时，其他时候可能没有。

我正在寻找某种方法来始终从该元素中检索日期值。使事情复杂化的是，数据库实例类型之间的结果似乎不同。兼容级别为 2005 的 2005 实例与兼容级别为 2005 的 2008R2 实例的行为不同。

这是一个示例查询，用于简化问题的演示。有 3 个不同的日期元素，一个具有相同日期，一个具有时区标识，第三个使用“无”/空日期格式:

DECLARE @p_LogInfo XML, @datetimeval Varchar(50), @tzdatetimeval Varchar(50);

set @datetimeval='2013-07-01T14:27:00.454725' 
set @tzdatetimeval='2013-07-01T14:27:00.454725Z' 
set @p_LogInfo = '<processLog xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.somthing.com"><notzdatetime>' + @datetimeval + '</notzdatetime><tzdatetime>' + @tzdatetimeval + '</tzdatetime><nulldatetime i:nil="true"/></processLog>';

WITH XMLNAMESPACES (DEFAULT 'http://www.somthing.com')
SELECT 
tbl.UPD.value('xs:dateTime(notzdatetime[1])', 'datetime') as no_tz_date
,tbl.UPD.value('notzdatetime[1]', 'datetime') as no_tz_date2 
,tbl.UPD.value('xs:dateTime(tzdatetime[1])', 'datetime') as tzdate 
,tbl.UPD.value('tzdatetime[1]', 'datetime') as tzdate2 
,tbl.UPD.value('xs:dateTime(nulldatetime[1])', 'datetime') as nulldate 
,tbl.UPD.value('nulldatetime[1]', 'datetime') as nulldate2 
FROM
@p_LogInfo.nodes('/processLog') AS tbl(UPD)

结果如下:

SQL Server 实例:2008R2 - 数据库兼容级别:2005 (90)

no_tz_date --Query Successful but returns NULL
no_tz_date2 --SUCCESSFUL DATE
tzdate --SUCCESSFUL DATE
tzdate2 --SUCCESSFUL DATE
nulldate --Query Successful but returns NULL
nulldate2 --SUCCESSFUL returns '1900-01-01 00:00:00.000'

SQL Server 实例:2005 - 数据库兼容级别:2005 (90)

no_tz_date --Query Successful but returns NULL
no_tz_date2 --(ERROR: Conversion failed when converting datetime from character string.)
tzdate --SUCCESSFUL DATE
tzdate2 --(ERROR: Conversion failed when converting datetime from character string.)
nulldate --Query Successful but returns NULL
nulldate2 --(SUCCESSFUL returns '1900-01-01 00:00:00.000')

我的问题是如何以简单通用的方式接收 xml 数据并设置日期格式？此日期存储在 SQL Server 的日期时间字段中。它始终以 UTC 时间存储。

Answer 1

我找到了一种方法来做到这一点，但我真的不喜欢这个过程。我在 SQL 中创建了一个简单的标量函数，它将日期作为字符串接收并将其限制为 23 个字符。如果字符串不为 null 或为空，则使用 CONVERT to datetime。然后在我有约会的任何地方，我都会围绕它包装这个功能。如果有人有更好的选择，请告诉我。它有效，但似乎是一个 hack :(

例子:

SELECT [dbo].GetFormattedDate('2013-07-01T14:27:00.434')
,[dbo].GetFormattedDate('')
,[dbo].GetFormattedDate(NULL)
,[dbo].GetFormattedDate('2013-07-01T14:27:00.434Z')
,[dbo].GetFormattedDate('2013-07-01T14:27:00.434445Z')
,[dbo].GetFormattedDate('2013-07-01')

函数代码:

CREATE FUNCTION [dbo].[GetFormattedDate] 
(  
    @p_DateString varchar(23)  
)
RETURNS datetime 
AS
BEGIN  

/* 
Notice that the in parameter truncates all datetime values to 23 characters
Which would truncate the date time to the thousandths place of the milliseconds
*/
DECLARE @returnDatetime as datetime;

IF ((@p_DateString IS NULL) OR (@p_DateString = '')) 
    BEGIN
        /* Return null is string is empty or null already */
        SET @returnDatetime = NULL;
    END
ELSE
    BEGIN
        /* Otherwise conver the string to a datetime */
        SET @returnDatetime = CONVERT(datetime, @p_DateString);
    END

RETURN @returnDatetime;

END