70-461 notes #2 – demo-ing the date datatypes

Handbook to Salisbury Cathedral - South ViewI’ve had a play with the date and time data types:

create table demo_dates
(demo_datetime datetime,
demo_datetime2 datetime2,
demo_date date,
demo_time time,
demo_datetimeoffset datetimeoffset)

insert into demo_dates
select GETDATE(),
GETDATE(),
GETDATE(),
GETDATE(),
GETDATE()

   select * from demo_dates</code>

Gives:

2014-12-15 16:50:02.247 2014-12-15 16:50:02.2470000 2014-12-15  16:50:02.2570000    2014-12-15 16:50:02.2570000 +00:00

I presume that the datetime2 field is showing 0000 after the millisecond because that's the extent of the precision of get_date().

Update: The presumption is justified 🙂

If you use sysdatetime() rather than getdate()....:

update demo_dates set demo_datetime2 = SYSDATETIME()

...then the datetime2 field gets populated down to the 7th decimal place

2014-12-15 16:50:02.247 2014-12-15 16:57:14.4219334 2014-12-15  16:50:02.2570000    2014-12-15 16:50:02.2570000 +00:00

If you try to stuff sysdatetime into an old style datetime field:

update demo_dates set demo_datetime = SYSDATETIME(),
demo_datetime2 = SYSDATETIME()

...then sqlserver is happy enough - it just truncates it:

2014-12-15 17:02:41.983 2014-12-15 17:02:41.9836863 2014-12-15  16:50:02.2570000    2014-12-15 16:50:02.2570000 +00:00
Advertisements