How to handle SQL DateTime conversion from XML input in general

Question

I have data included in a stored procedure as XML. One of the elements is the DateTime value. Sometimes, when I get a value, it has a time zone designation of "Z", there may not be another time.

I am looking for a way to get the date value from this element. To complicate matters, it seems that the results differ between the types of database instances. 2005 with the 2005 compatibility level is different than the 2008R2 instance with the 2005 compatibility level.

Here is an example request to simplify the demonstration of the problem. There are three different date elements with the same date: one with the time zone and one using the zero / zero 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 Compatibility 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 Compatibility 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 the xml data and format the date in a simple way? This date is stored in the datetime field on the SQL server. It is always stored in UTC.

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 a date as a string and limits it to 23 characters. Then CONVERT is used for datetime if the string is not empty or empty. Then wherever I meet the date, I wrap this function around it. If anyone has a better option let me know. It works, but it looks 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