Date and Time Manipulation in SQL Server

• Date and Time Datatypes and Internals
  DateTime(8 bytes): 01/01/1753 to 12/31/9999, accuracy 3+1/3rd ms(nearest timetick)
  SmallDatetime 01/01/1900 to 6/6/2079, accuracy is 1 minute
• Language Dependency
  How to express datetime literals – determined by the login setup:
  Can use Set language command
  Can use Set dateformat command to what you want so the query will run.
  Should use language neutral forms of the date: ‘20030201’ Can set the date to anything, and it will still read it properly.
  Select * from sales.salesorderheader where orderdate = ‘20030201’
• Finds the fist day of the week in the US
  Select *
  From sales.salesorderheader
  Where datepart(weekday,orderdate + @@datefirst) = 3
  No Separation between Date and Time
  Date only: Convert(Varchar(8), ,dateformat)
  Select cast(convert(char(8),getdate(),112) as datetime) 112 is language neutral. Old method.
  Select dateadd(datediff(day, 0, getdate()),0) any integer will be midnight.
• New Date and Time support in SQL Server 2008
  DATE – includes only the Date Range 01-01-0001 and 12-31-9999
  TIME – includes only the time. Not normally needed.
  DATETIME2 – changes the range to 01-01-0001 and 12-31-9999
• DATETIMEOFFSET - can include the time zone

Summary: New Date and Time support ends the HR Vista issues that we have seen. I am not sure if this is good or bad, because we will be allowing bad dates into our databases.