XML Schema dateTime and SQL Server 2005

Posts   
 
    
Marcus avatar
Marcus
User
Posts: 747
Joined: 23-Apr-2004
# Posted on: 03-Sep-2006 12:09:58   

Not directly an LLBLGen related question but I thought I'd post here anyway!

Has anyone used SQL Server 2005's XML schema with dateTime values?

The following "should" work, but for the life of me I can't figure out what Im doing wrong:

CREATE XML SCHEMA COLLECTION [dbo].[TestDateSchema] as 
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="TestDate" type="xsd:dateTime"/>
</xsd:schema>'
GO

DECLARE @Test XML(TestDateSchema);
SET @Test=CONVERT(XML,N'<TestDate>2001-01-01T00:00:00</TestDate>');
GO

DROP XML SCHEMA COLLECTION [dbo].[TestDateSchema]
GO

It produces the following error:

Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: '2001-01-01T00:00:00'. Location: /*:TestDate[1]

Its obviously something to do with date formatting - Any ideas?

Marcus

Marcus avatar
Marcus
User
Posts: 747
Joined: 23-Apr-2004
# Posted on: 03-Sep-2006 12:14:24   

...and just like that it was solved! smile

See http://www.w3.org/TR/NOTE-datetime for info on ISO 8601 date formatting.

SET @Test=CONVERT(XML,N'<TestDate>2001-01-01T00:00:00Z</TestDate>');

Note the "Z" character for UTC time zone.