July 13, 2009 at 6:52 pm
Hi,
In SQL Server 2008 "breaking changes" list I found this:
In SQL Server 2005, the data types xs:time, xs:date, and xs:dateTime do not have timezone support. Timezone data is mapped to the UTC timezone. SQL Server 2008, provides standard conformant behavior, resulting in the following changes:
Values without timezone are validated.
The provided timezone or the absence of a timezone is preserved.
The internal storage representation is modified.
Resolution of stored values is increased.
Negative years are disallowed.
Modify applications and XQuery expressions to account for the new type values. For more information, see Using XML with time, date, datetime2, and datetimeoffset Data Types.
What can actually break if you upgrade from SQL Server 2005 to 2008 because of this.
Thanks.
July 30, 2009 at 4:12 am
nothing, the old datetime datatype is still supported in SQL-Server 2008
August 2, 2009 at 9:27 am
In 2005 the following xquery expression results in "
".
In 2008 it results in "
".
This seems to have nothing much to do with the SQL Server DATETIME type but maybe they share some of the same code base, which might explain (but not really excuse) the breaking change in 2008.
DECLARE @x XML;
SET @x='';
SELECT @x.query('element dt{ xs:dateTime("2009-08-02T00:00:00+01:00")}') x;
January 14, 2010 at 12:59 pm
This is the big one,
declare @xml as xml
set @xml = '<value>0001-01-01T00:00:00</value>'
In 2005
@xml.value('xs:dateTime(value[1])', 'datetime') results in NULL
so passing in DateTime.MinValue from .net where MinValue represented null works quite nicely (although post 2.0 you should be using Nullable<DateTime>.
In 2008
@xml.value('xs:dateTime(value[1])', 'datetime') results in a type conversion error as xs:dateTime now returns a datetimeoffset which is then attempted to be converted to a datetime that has a min year of 1753.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply