Dear all, thank you for all your responses.

Jonathon Prosper: I'm not sure how datetimeoffset is stored Jonathon, but I'm guessing that 6-8 bytes are used to store the date and time elements (YYYY-MM-DD hh:mm:ss[.nnnnnnn]), whilst 2 bytes are used to store the timezone ([+|-]hh:mm). Whether it take 6, 7 or 8 bytes depends on the precision of the fractional second element (the .nnnnnnn), i.e. if the precision is 0-2 decimal points it is 6 bytes, if the precision is 3-4 decimal points it is 7 bytes and if the precision is 5-7 decimal points it is 8 bytes.

When a variable of datetimeoffset data type is implicitly converted to datetime data type, the result is the date and time components of the local datetime. For example, if the datetimeoffset is 2007-10-26 14:54:23.1234567 -06:00 then the datetime is 2007-10-26 20:54:23.123.

For explicit conversion we have the option of specifying the style. Style 0 means we want the output in local datetime (in the above example the output is 2007-10-26 20:54:23.123) whereas style 1 means that we want the output in UTC (in the above example the output is 2007-10-26 14:54:23.123)

Michel Stainer: Yes Michel you are right, it should be 31st December 9999. Thank you for correcting it.

BanzaiSi: Yes you are right BanzaiSi, the name of the function is CURRENT_TIMESTAMP, not CURRENT_DATETIME. Thank you for highlighting this mistake.

John Nolan: Please see below regarding datetime2 naming.

Nebojsa Ilic: Thank you for posting the storage size. See below regarding datetime2 naming.

Daniel Wolford: all the existing styles arguments for CONVERT function (0 to 131) work with the new data types. For example, to trim the nanoseconds part, we can do "convert (varchar, TIME, 108)" to produce hh:mm:ss or "convert (varchar, DATETIME2, 120)" to produce YYYY-MM-DD hh:mm:ss.

rswinehart: It seems that in 2008 ISDATE() would still be the only function that determines whether a variable is a valid date or time value. I agree that ISSMALLDATETIME

---------------------------------------------------------------------------

John Nolan, Nebojsa Illic, JJ B, Jamie Thompson, Joe Barbian and Ian Yates regarding DATETIME2 NAMING:

When I heard DATETIME2 I was also immediately thinking about Oracle's VARCHAR2. So I agree with you all that it's not a good name. I also agree that BIGDATETIME seems to be a more suitable name, as we hae SMALLDATETIME, SMALLINT, INT and BIGINT, as Jamie Thomson correctly mentioned.

On 21/8/2007 Eland Somarskog mentioned on Microsoft Connect (http://connect.microsoft.com) that datetime2 was not a very good name for a data type, for example when we needed to say "datetime2(4)". He mentioned BIGDATETIME and NEWDATETIME but prefers DATEANDTIME. The reasoning was BIGDATETIME is misleading as DATETIME2 may be smaller than DATETIME, and NEWDATETIME will not be new in 2020.

Microsoft's response was:

"The naming decision we made was mainly based on:

- demonstrating the transparency of the existing DATETIME type

- ensuring the new type name can be general enough to represent all new enhancements

Except for TIMESTAMP (ANSI SQL standard name for datetime), I should say we don’t see there is perfect name in the world about it. Unfortunately, TIMESTAMP is already taken for something else in SQL Server. After giving all the considerations, we believe that DATETIME2 is the best name against our guideline."

I think Jamie and Eland is right that hopefully we will have an alias for DATETIME2.

----------------------------------------------------------------------------

Jonathon Prosper, thanks for your explanation regarding Tibor Karaszi explanation about why 1753 is the earliest date for datetime. I found it very useful.

http://www.karaszi.com/sqlserver/info_datetime.asp#Why1753

Kind regards,

Vincent Rainardi