Dear Dan

Thanks for your feedback. You are indeed correct that XML Schema allows values with and without timezone to be validated and processed and that our other validators support it. However, since the SQL Server architecture does not support such a complex datetime type yet, we had to make an implementation decision on whether to support only values with or without timezone in the SQL Server 2005 release. After consulting several hundred beta customers we decided to require the timezone.

Current workarounds include:
1. Change the schema type to xs:string (you will lose operational semantics)
2. Store the XML data without a schema constraint, perform the schema validation on the mid-tier or use the SQLCLR to write a function that passes the XML and XML Schema to the validator.
3. Make sure that the generated values associate a timezone.

We are working on resolving this for one of our future release of SQL Server 2005, although at this stage I cannot give any specific release.

Thanks for reporting the issue. Feedback like this certainly helps us improving the product.

Best regards
Michael

It is my pleasure to announce that SQL Server 2008 starting in the July CTP provides complete support for timezone and non-timezone aware XSD values.

We appreciate the feedback that helped us prioritize the work, and hope that you will find the changes in SQL Server 2008 to be to your liking.

Best regards
Michael

Michael, thank you for the explanation (also the one posted in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=633912&SiteID=1) the explanation were helpful in determining what is causing my pain right now.

We import data in XML format into SQL Server for processing later. If we specify the timezone when we create the data as "Z" then once it gets to the UI it is incorrect -- we do not want it to be UTC, but to be the timezone of the server where we are importing it. However we are converting into xsd:datetime format in an XSLT that runs from SSIS before it gets to the server, so now it looks like I need to find an XSLT solution to determine the current timezone of the server.

The really strange part to me is that the handling of timezones in SQL was not how I anticipated, especially given this issue. For example:

SELECT getdate(), getutcdate() -- CONFIRM SERVER NOT IN UTC (VALUES DIFFERENT)
SELECT { ts '1998-05-02 01:23:56.123' } -- SHOULD BE LOCAL, I THOUGHT
SELECT CAST('1998-05-02T01:23:56.123Z' AS DATETIME) -- SHOULD BE UTC
SELECT CONVERT(DATETIME, '1998-05-02T01:23:56.123Z') -- SHOULD BE UTC
SELECT CONVERT(DATETIME, '1998-05-02T01:23:56.123Z', 127) -- SHOULD BE UTC
SELECT CAST('1998-05-02 01:23:56.123' AS DATETIME) -- SHOULD BE LOCAL, I THOUGHT

So there is probably still quite a bit for me to learn about SQL's handling of time zones, either that, or the handling of xsd:datetime is just completely inconsistent with the rest of SQL Server and .NET... in either case, this is a major source of pain on my current project and I am spending hours on an issue that I would have thought would take minutes.

Hi Lex, first my apology for not answering sooner, but if you comment on a closed feedback item, it is very unlikely that we see your comment.

Now to your issue at hand: You need to check for the server timezone if you want to add the timezone on the client. Also, I would recommend to look at SQL Server 2008 which has added considerable support for better date time handling both in SQL and XML.

Best regards
Michael