CREATE BLOG dbo.SqlServer

Here's our first Question and Answer in our series on DBA Job Interview Questions:

Describe the difference between DATETIME and SMALLDATE and describe when you would use each

My answer would first start out with an overview of what the basic differences are - I would keep it high level and then, if they want more detail, I would explore deeper. The minor differences are the DATETIME is 8 bytes and SMALLDATETIME is 4 bytes as well as the fact that the date ranges in DATETIME are larger (1753 - 9999) than SMALLDATETIME (1900-2079).

Another more subtle difference is that SMALLDATETIME only stores/works-with minutes; it does not handle seconds or milliseconds whereas DATETIME uses milliseconds. The documentation talks about the accuracy of these but, to me, it's more important to understand that if I need to compare/store seconds, I am forced in to using DATETIME.

Lastly, another even-more-subtle difference is that SMALLDATETIME will round up/down to the nearest minute whereas DATETIME will round up/down to the nearest 3/1000th of a second.

The second part of this, "describe when you would use each", is sort of answered by the explanations above, isn't it? I would tell the interviewer that, if I needed data before 1900 or after 2079, the of course I'mn using DATETIME. If I need to store/compare the seconds, then I'll use DATETIME.

This post assumes that you know a little bit about dates and times in SQL Server - that's sort of beyond this post to teach you what they are - so this post just focuses on answering the interview question.

Hope this helps you!

---

Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server