Connect Digest : 2009-10-09

This past week I was reviewing a few bugs surrounding the new DATE, TIME, DATETIME2 and DATETIMEOFFSET data types, introduced in SQL Server 2008.  While they are much more like first-class citizens than the CLR-based types that Microsoft tried unsuccessfully to introduce with SQL Server 2005, there are still a variety of integration problems that make them very difficult candidates for users to switch from the old standby DATETIME and SMALLDATETIME types.  Rather than give my spin on every single one of them, I thought I would just list them here and allow you to discern which ones interest you on title alone, without any of my commentary.  Well, at least here; you may see my input on some of these items.

#327335 : ISDATE function doesn't work on DATE datatypes

#355655 : Conversion failed when converting date and/or time from character string.

#350554 : Datetime2 and sysdatetime() truncate nanoseconds

#495402 : Reporting Services Rounds Parameters of Data Type Datetime2(7) to Datetime

#363229 : Confusing and undocumented behavior changes in some expressions involving literal dates

#290021 : Datetime and Datetimeoffset interpret literal strings differently

#291184 : Drop extra precision when converting date from string

#341840 : SYSDATETIME and SYSUTCDATETIME exhibit loss of datetime2 fractional second precision

#320897 : SSIS Expression Date Functions Incomplete - Missing New Data Type Support

#290011 : <Date value> + <number> should be supported, at least for integers

#290014 : Valid (ISO standard) date arithmetic fails

#320998 : Add datediff_big

#290971 : Deprecate the date literal interpretation 'YYYY-DD-MM' except in compatibility modes <= 90

#356470 : Support syntax for typed literal values

#293333 : Add sysdate()

#308563 : time zone conversion function

#354816 : ISDATE() does not recognize date values with higher precision (DATETIME2, DATETIMEOFFSET)

#354818 : Need a function that takes a DATE and TIME parameters and returns a DATETIME2 value.

#339595 : Easier conversion from DateTimeOffset to client computer local time

#316457 : The Datatypecompatibility property should not affect date and datetime2

#291129 : Support column type conversion from date to datetimeoffset in SSMS

#290269 : Confusing DATEDIFF behavior/documentation discrepancy

#290348 : Implicit conversion for DATETIME2?