Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Archive for June 9th, 2009

Plenty of new features in iPhone 3G S

Posted by decipherinfosys on June 9, 2009

There are plenty of new features in the new version of the iPhone.  Our favorite ones are internet tethering, find my iPhone, a much needed improved battery life and more storage and RAM.  You can read more on the feature sets in the apple blog here.  Though the phone is wonderful, the network that supports it is aweful.  You can read more on that on Om Malik’s blog.  And here is another post at gigaom which talks about how AT&T’s new network may not help the old iPhone users.  Maybe it is time for Apple to partner with someone besides AT&T.

Posted in News, Technology | Leave a Comment »

The “T” in datetime value in XML

Posted by decipherinfosys on June 9, 2009

While working on an integration project, client’s developer had asked about the “T” separator between date and time value in the datetime data type fields and also about the default date of 1900-01-01 which we have covered in our blog posts before – here.  Also, note that an empty string is different than NULL value – the default value for an empty datetime string is converted to 1900-01-01 00:00:00.000 but for a NULL value, unless you are using XSINIL or coalescing that field to be an empty string, that element will not be present in the output. You can read more on the difference between an empty string and NULL over here.

Regarding the “T” separator, that is the ISO8601 format and is the default way of representing the datetime data in XML.   When you generate the data out of the database for a datetime data-type field, you will automatically get the “T” separator – here is an example:

declare @local_xml_tbl table (col1 int identity, colx datetime)
insert into @local_xml_tbl (colx) values (getdate())
select * from @local_xml_tbl

And the output will be:


As BOL states, this is ISO 8601 standard for combined date and time in UTC.   Now, what happens when we consume the data and shred an XML document to consume it in the relational system and that XML contains datetime fields in this format?  Does it still display the “T” after the shreding or does it take care of removing after we have applied one of the shreding methods to it like the nodes() method?  Let’s take a look at that using an example:

DECLARE @x xml

SET @x ='<VoteAgent>

T.VA.value (‘../MtgNtfcnId[1]’,’varchar(20)’ ) AS Mtng_Notification,
T.VA.value(‘Key[1]’, ‘bigint’) as Share_Holder_ID,
T.VA.value(‘Shares[1]’, ‘numeric(15,5)’) as Votable_Shares,
T.VA.value(‘MeetingDateTime[1]’, ‘datetime’) as Meeting_Date_Time,
T.VA.value(‘RecordDateTime[1]’, ‘datetime’) as Record_Date_Time,
T.VA.value(‘VoteAction[1]’, ‘varchar(10)’) as Vote_Action
from @x.nodes(‘//Details’) as T(VA)

Mtng_Notification    Share_Holder_ID      Votable_Shares                          Meeting_Date_Time       Record_Date_Time        Vote_Action

——————– ——————– ————————————— ———————– ———————– ———–

444                  987654321            100.00000                               1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 FOR

999                  45454545454          657.00000                               2009-01-12 20:10:00.000 2008-12-22 10:10:00.000 FOR

So, as you can see, the “T” is no longer present after we consumed the XML.

If for some reason, there is a need to display the datetime data without the “T” in XML, you can always use the CONVERT() function to convert the datetime field to a string with a different format.


  • Wiki Post on ISO 8601 – here.
  • BOL article on the CONVERT() function – here.  Take a look at the different formats.  You will see 126 as the one for ISO 8601.

Posted in SQL Server | Leave a Comment »