SQL Server 2008 DateTime and DateTime2 behavior – have you seen this?

Hi Friends,

You would know that SQL Server 2008 DateTime and DateTime2 data type amongst many new data types. Here is an interesting observation. Comments are welcome:

When you run the following code with the default date format setting, you get the right output:

declare @dt datetime='2012-11-04'
declare @dt2 datetime2='2012-11-04'
selectMONTH(@dt)as [Month-Datetime],DAY(@dt)as [Day-Datetime]
selectMONTH(@dt2)as [Month-Datetime2],DAY(@dt2)as [Day-Datetime2]
go

But when you change the format to dmy; you get a different output 🙂

setdateformat dmy
go
 
-- different with ANSI string format assignment
declare @dt datetime='2012-11-04'
declare @dt2 datetime2='2012-11-04'
selectMONTH(@dt)as [Month-Datetime],DAY(@dt)as [Day-Datetime]
selectMONTH(@dt2)as [Month-Datetime2],DAY(@dt2)as [Day-Datetime2]
go

Comments are welcome.