locked
T-SQL DateTime Trouble RRS feed

  • Question

  • Hello! 

    I'm having troubles parsing DateTime in SQL Server 2005.

    I'm using this query to extract datetime:

     

    declare @xml xml
    set @xml = 
    '<s d="Winpac10" o="NED" sd="12/22/2010 00:24:21" dd="12/22/2010 01:24:19" sid="0">
     <p id="1">1</p>
     <p id="2">1</p>
     <p id="3">1</p>
     <p id="4">1</p>
     <p id="5">1</p>
     <p id="6">1</p>
     <p id="7">1</p>
     <p id="8">1</p>
     <p id="9">1</p>
     <p id="10">1</p>
     <p id="11">1</p>
     <p id="12">1</p>
     <p id="13">1</p>
     <p id="14">1</p>
    </s>'
    
    declare @deviceName nvarchar(128)
    declare @objectName nvarchar(128)
    declare @deviceDate datetime
    declare @serverDate datetime
    
    set @deviceName = @xml.value('(s/@d)[1]','nvarchar(128)');
    set @objectName = @xml.value('(s/@o)[1]','nvarchar(128)');
    set @serverDate = getdate();
    set @deviceDate = @xml.value('(s/@dd)[1]','datetime');
    
    select 
    	T.s.value('@id[1]','nvarchar(200)') paramID, T.s.value('.','nvarchar(200)') paramValue
    from @xml.nodes('s/p') T(s)

     

     

    DateTime strings are generated with perfectly culture independent mechanism:

     

        XElement root = new XElement("s",
         new XAttribute("d", ID), // device name
         new XAttribute("o", Parent.ID), // object name
         new XAttribute("sd", DateTime.Now.ToString(CultureInfo.InvariantCulture)), // server date
         new XAttribute("dd", data.Timestamp.ToString(CultureInfo.InvariantCulture)), // device date
         new XAttribute("sid", data.SubscriptionID)
         );
    

     

    But SqlServer raises conversion error Msg 8115, Level 16 when I attempt to parse datetime 

    Any help will be more then helpful! Thanks!

     


    God bless you!
    Tuesday, December 21, 2010 9:57 PM

Answers

  • Well as you can see I use c# based code to generate XML and pass it to SQL Server 2005. According to MSDN (http://msdn.microsoft.com/en-us/library/ht77y576.aspx) or http://msdn.microsoft.com/en-us/library/4c5zdc6a(v=VS.100).aspx I'm using Culture Independent datetime format. This is done intentionally since I don't want any culture-specific problems.

    Well, the format you had is certaintly not culture-independent. As I said, most people would not understand it. I'm not much of a .Net programmer, but I noticed some time back when I actually had the need to format date values with ToString, that I had to specify the format explicitly to be sure that I got something useful. What they touted as "invariant culture" was something else.

    From your post it seems that Sql Server 2005 uses it's own conventions about culture independent datetime format (e.g. ISO 8601):

    SQL Server does indeed has some funny conventions of its own, in that YYYY-MM-DD may be interpreted as something else. (Althought the new data types added in SQL 2008 addresses that.)

    I suppose I should hardcode ISO format in my c# based app and pass ISO format string along with my xml.

    That was what I did in my .Net application I worked with a while back.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by tillias Wednesday, December 22, 2010 9:42 AM
    Wednesday, December 22, 2010 9:03 AM

All replies

  • This code works fine for me. Did you try 

    SET DATEFORMAT mdy 

    before running it?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, December 21, 2010 10:38 PM
  • set @xml =
    '<s d="Winpac10" o="NED" sd="12/22/2010 00:24:21" dd="12/22/2010 01:24:19" sid="0">

    The preferred dateformat in XML is YYYY-MM-DDThh:mm:ss. This format is also one of the safe date formats in T-SQL. The other is YYYYMMDD. (But not YYYY-MM-DD with out the T.)

    DateTime strings are generated with perfectly culture independent mechanism:

    It does not seem like that, since that dateformat is understood in very few cultures. 22 months in a year?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Tuesday, December 21, 2010 11:10 PM
  • DateTime strings are generated with perfectly culture independent mechanism:

    It does not seem like that, since that dateformat is understood in very few cultures. 22 months in a year?



    Thanks for you answer!

    Well as you can see I use c# based code to generate XML and pass it to SQL Server 2005. According to MSDN (http://msdn.microsoft.com/en-us/library/ht77y576.aspx) or http://msdn.microsoft.com/en-us/library/4c5zdc6a(v=VS.100).aspx I'm using Culture Independent datetime format. This is done intentionally since I don't want any culture-specific problems.

    From your post it seems that Sql Server 2005 uses it's own conventions about culture independent datetime format (e.g. ISO 8601):

    http://msdn.microsoft.com/en-us/library/ms187819.aspx -- "The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting."

    I suppose I should hardcode ISO format in my c# based app and pass ISO format string along with my xml. E.g. CultureInfo.InvariantCulture.DateTimeFormat.SortableDateTimePattern


    God bless you!
    Wednesday, December 22, 2010 5:36 AM
  • Well as you can see I use c# based code to generate XML and pass it to SQL Server 2005. According to MSDN (http://msdn.microsoft.com/en-us/library/ht77y576.aspx) or http://msdn.microsoft.com/en-us/library/4c5zdc6a(v=VS.100).aspx I'm using Culture Independent datetime format. This is done intentionally since I don't want any culture-specific problems.

    Well, the format you had is certaintly not culture-independent. As I said, most people would not understand it. I'm not much of a .Net programmer, but I noticed some time back when I actually had the need to format date values with ToString, that I had to specify the format explicitly to be sure that I got something useful. What they touted as "invariant culture" was something else.

    From your post it seems that Sql Server 2005 uses it's own conventions about culture independent datetime format (e.g. ISO 8601):

    SQL Server does indeed has some funny conventions of its own, in that YYYY-MM-DD may be interpreted as something else. (Althought the new data types added in SQL 2008 addresses that.)

    I suppose I should hardcode ISO format in my c# based app and pass ISO format string along with my xml.

    That was what I did in my .Net application I worked with a while back.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by tillias Wednesday, December 22, 2010 9:42 AM
    Wednesday, December 22, 2010 9:03 AM