Just about everything you ever wanted to know about SQL Server Date and Time Data Types...

CodeProject - Date and Time Data Types and Functions - SQL Server (2000, 2005, 2008, 2008 R2, 2012)

Introduction

It would be better, if we know the different functions available with SQL Server for date and time. So that we can make use of functions properly in different scenarios. In this article we will go through all the available date and time data types and functions with sample codes. This article will list down the available date and time data types and functions for SQL Server version from 2000 and above.  If you want more info on any items then click on the 'read more details' of that section and that link will take you to MSDN site.  

Table Of Contents 

• Introduction
• Background
• Date and Time Data Types - Quick Reference
• Date and Time Data Types - In Detail
  • time
  • date
  • smalldatetime
  • datetime
  • datetime2
  • datetimeoffset
• Date and Time Functions - Quick Reference.
  • Date and Time Functions - SQL Server 2000 and 2005
  • Date and Time Functions - SQL Server 2008 and 2008 R2
  • Date and Time Functions - SQL Server 2012
  • Date and Time Functions - SQL Server
• Date and Time Functions - In Detail
  • GETDATE
  • CURRENT_TIMESTAMP
  • GETUTCDATE
  • SYSDATETIME
  • SYSDATETIMEOFFSET
  • SYSUTCDATETIME
  • DATENAME
  • DATEPART
  • DAY
  • MONTH
  • YEAR
  • DATEADD
  • DATEDIFF
  • ISDATE
  • EOMONTH
  • SWITCHOFFSET
  • TODATETIMEOFFSET
  • DATEFROMPARTS
  • DATETIME2FROMPARTS
  • DATETIMEFROMPARTS
  • DATETIMEOFFSETFROMPARTS
  • SMALLDATETIMEFROMPARTS
  • TIMEFROMPARTS
  • @@DATEFIRST
  • SET DATEFIRST
  • SET DATEFORMAT
• Functions that can affect the output of date functions
  • @@LANGUAGE
  • SET LANGUAGE
  • sp_helplanguage
• Functions that helps to convert date and time values to and from string literals and other date and time formats.
• Interesting Date and Time Questions
• References
• Summary

Background

The main objective of this article is to give a common place for all date and time data types and functions for all SQL Server versions so that we don't need to jump from one page to another. This article will help you to understand the different functions available with different versions of SQL Server with simple examples (All examples are self descriptive). At the same time this article can be used as a quick reference too. 

...

..."

We're talking 21 pages of Date Time Data Type text... yeah, wow. I love the comparison across versions matrixes.