posted 4/3/2012 10:16:00 AM by Amit Bansal - Views: [7092]
Hi Friends,
You would know that SQL Server 2008 introduced 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.
If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks
Have a SQL Server question? Join the fastest growing SQL Server facebook group at: http://www.facebook.com/groups/458103987564477/
Regards,
Amit Bansal
http://www.twitter.com/A_Bansal
http://www.twitter.com/SQLServerGeeks
http://www.amitbansal.net/
Visit my FaceBook page at http://www.facebook.com/AmitRSBansal
Contribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us
Amit Bansal (Member since: 3/12/2011 4:59:54 PM) Follow Amit at Twitter @A_Bansal : Amit Bansal is the CTO of eDominer Systems & Peopleware India. He is a consultant, trainer, writer, speaker & evangelist on SQL Server & Business Intelligence. A seasoned speaker; he speaks at major summits and conferences of Microsoft worldwide including TechED, MCT Summits, MSDN & TechNet conferences. Over the last 8 years, he has consulted, trained & mentored more than 6000 IT professionals on SQL Server & Business Intelligence and worked with top notch blue chip companies worldwide including Microsoft, Infosys, Wipro, RBS, HCL, HP, Siemens, IBM, Accenture, etc. He has delivered more than 400 workshops on SQL Server & Business Intelligence. Apart from holding many Microsoft credentials, he is also a Microsoft Most Valuable Professional (MVP) awardee on SQL Server. He is also one of the first Microsoft Certified Trainer in India. Amit has worked with Microsoft in India and US as a Subject Matter Expert in various capacities participating in OD sessions, technical reviewing, etc. He is also a very active community lead in Asia Pacific. Visit www.amitbansal.net to know more. Subscribe to Amit’s blog and be a member of his technical forum at www.SQLServerGeeks.com - an exclusive SQL Server portal. Visit www.peoplewareindia.com for Corporate Training solutions. FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346 LinkedIn – http://www.linkedin.com/pub/amit-bansal/7/121/755
View Amit Bansal 's profile
Makes you wonder what they mean in the SET DATEFORMAT books online:
"DATEFORMAT setting on the interpretation of character strings might be different for datetime and smalldatetime values than for date, datetime2 and datetimeoffset values, depending on the string format. This setting affects the interpretation of character strings as they are converted to date values for storage in the database. It does not affect the display of date data type values that are stored in the database or the storage format."
In general, leave formatting of dates to render time.
This applies to SSRS reports etc, where you also need to set the correct language on the rdlc file, otherwise your datepicker for variables gives US date formats in the parameter window.
Thsi has to do with the set dateformat option. This isn't valid for datetime2 format. You will see if you are using higher numbers for the day, you will get an error, that this isn't a valid month.
Hopes this explains it.
Leave a comment