How to handle SQL DateTime conversion from XML input in a common way

Question

I have data coming into a Stored Procedure as an XML. One of the elements is a DateTime value. Sometimes when I get the value it has the "Z" timezone designation, other times is might not.

I am looking for some way to always retrieve the date value from this element. To complicate things it appears that the results are different between database instance types. 2005 instance with 2005 compatibility level acts differently than 2008R2 instance with a 2005 compatibility level.

Here is a sample query to simplify the demonstration of the issue. There are 3 different date elements one with a same date, one with a time zone designation and a 3rd using the "nil"/null date format:

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)

Here are the results:

SQL Server Instance: 2008R2 - Database Compat level: 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 Instance: 2005 - Database Compat level: 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')

My question is how can I take in the xml data and format the date in a simple common manner? This date is store in a datetime field in SQL server. It is always stored a in UTC time.

Answer 1

I found a way to do this but I really don't like the process. I created a simple scalar function in SQL that takes in the date as a string and restricts it to 23 characters. Then a CONVERT to datetime is used if the string is not null or empty. Then everywhere I have a date coming in I wrap this function around it. If someone has a better option let me know. It works but, seems like a hack :(

Example:

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')

Function Code:

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