Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113

    Unanswered: Datetime values when using for XML Path

    Consider the following test case:

    Code:
    CREATE TABLE DateTest (Test_Date DATETIME)
    
    INSERT INTO DateTest VALUES ('2010-05-20')
    INSERT INTO DateTest VALUES ('2010-06-18')
    INSERT INTO DateTest VALUES ('2010-07-03')
    INSERT INTO DateTest VALUES ('2010-07-21')
    INSERT INTO DateTest VALUES ('2010-06-09')
    INSERT INTO DateTest VALUES ('2010-07-14')
    INSERT INTO DateTest VALUES ('2010-05-12')
    
    SELECT Test_Date FROM DateTest FOR XML PATH ('Row')
    Returns me some XML results as thus:
    <Row><Test_Date>2010-05-20T00:00:00</Test_Date></Row><Row><Test_Date>2010-06-18T00:00:00</Test_Date></Row><Row><Test_Date>2010-07-03T00:00:00</Test_Date></Row><Row><Test_Date>2010-07-21T00:00:00</Test_Date></Row><Row><Test_Date>2010-06-09T00:00:00</Test_Date></Row><Row><Test_Date>2010-07-14T00:00:00</Test_Date></Row><Row><Test_Date>2010-05-12T00:00:00</Test_Date></Row>
    Executing a basic select without the XML clause returns me tabular results as thus:
    2010-05-20 00:00:00.000
    2010-06-18 00:00:00.000
    2010-07-03 00:00:00.000
    2010-07-21 00:00:00.000
    2010-06-09 00:00:00.000
    2010-07-14 00:00:00.000
    2010-05-12 00:00:00.000
    Can anyone explain to me why the XML results have a T embedded in the middle of the field value. I'm making an obvious assumption that that is to define where the time portion starts. However it's causing problems in some of our XML data feeds off to third parties and I need to strip it.

    Does anyone know how to output datetime data in an XML format (I notice the same behavior when using XML RAW and XML AUTO) without this T stuck in the middle?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The "T" character is a "time introducer character" that is required to make the date format explicitly ISO standard. There are a few countries with date formats that are ambiguous without including the T, so the ISO standard (and therefore the XML definition) requires the presence of the T character.

    The only way that I know to remove the T character is to use the Convert() in order to explicitly format the time yourself.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Of course it's obvious when someone else tells you the answer.

    Thanks Pat.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Since you don't seem to care about the time portion, and providing the platform is sql2k8 or higher, - define Test_Date field as DATE, and your output with and without "FOR XML" will have the same format.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    The above data was sample data, I left the time blank for simplicity.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •