MS SQL XML - Date timezones

Author

Date timezones

McGeeky

2005-05-13, 11:23 am

When opening up an xml message using openxml and reading dates, SQL Server
reads the dates as is.

E.g if a date is defined as 2005-05-13 in your message then SQL Server will
treat it as 2005-05-13. However, BizTalk converts that date to 2005-05-12
because the timezone character, Z, is missing from the end. If the date were
specified as 2005-05-13Z then BizTalk would be happy with it but then SQL
Server would complain about invalid date formatting.

Now what is happening with SQL Server 2005? Will it start treating dates in
the same way as BizTalk? That is, if the Z is missing from the end of a date
it will apply a timezone conversion? If it does then it will not be backward
compatibe with 2000. As it is, SQL Server and BizTalk are already
inconsistent in the way that they handle dates.

Any views on this?

--
McGeeky
http://mcgeeky.blogspot.com

Michael Rys [MSFT]

2005-05-13, 11:23 am

OpenXML will stay backwards-compatible. And I do not see a reason to change
the date with a timezone given on the server. This looks like you happen to
have a locale where you get a different date from UTC.

You can use (and have to) timezones for the xs:datetime values on your XML
datatype and XQuery. So you can use that. But we are not locale-sensitive,
so if you cast such a value to the SQL datetime value, we only drop the Z
time (that is in alignment with the SQL standard semantics).

Best regards
Michael

"McGeeky" <anon@anon.com> wrote in message
news:OVyXmz8VFHA.3532@TK2MSFTNGP09.phx.gbl...
> When opening up an xml message using openxml and reading dates, SQL Server
> reads the dates as is.
>
> E.g if a date is defined as 2005-05-13 in your message then SQL Server
> will treat it as 2005-05-13. However, BizTalk converts that date to
> 2005-05-12 because the timezone character, Z, is missing from the end. If
> the date were specified as 2005-05-13Z then BizTalk would be happy with it
> but then SQL Server would complain about invalid date formatting.
>
> Now what is happening with SQL Server 2005? Will it start treating dates
> in the same way as BizTalk? That is, if the Z is missing from the end of a
> date it will apply a timezone conversion? If it does then it will not be
> backward compatibe with 2000. As it is, SQL Server and BizTalk are already
> inconsistent in the way that they handle dates.
>
> Any views on this?
>
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>
>

McGeeky

2005-05-16, 3:23 am

Thanks for the information Michael

--
McGeeky
http://mcgeeky.blogspot.com

"Michael Rys [MSFT]" <mrys@online.microsoft.com> wrote in message
news:OzCcTP9VFHA.628@tk2msftngp13.phx.gbl...
> OpenXML will stay backwards-compatible. And I do not see a reason to
> change the date with a timezone given on the server. This looks like you
> happen to have a locale where you get a different date from UTC.
>
> You can use (and have to) timezones for the xs:datetime values on your XML
> datatype and XQuery. So you can use that. But we are not locale-sensitive,
> so if you cast such a value to the SQL datetime value, we only drop the Z
> time (that is in alignment with the SQL standard semantics).
>
> Best regards
> Michael
>
> "McGeeky" <anon@anon.com> wrote in message
> news:OVyXmz8VFHA.3532@TK2MSFTNGP09.phx.gbl...
>
>

Sponsored Links