SQL Server 2008 Date and Time data types

No more converting datetimes and combining datetimes together because you wanted to seperate date and time. (EX: '1/1/1900 11:36AM')

Here are the new data types for SQL Server 2008:

Data type	Format	Range	Accuracy
time	hh:mm:ss[.nnnnnnn]	00:00:00.0000000 through 23:59:59.9999999	100 nanoseconds
date	YYYY-MM-DD	00001-01-01 through 9999-12-31	1 day
smalldatetime	YYYY-MM-DD hh:mm:ss	1900-01-01 through 2079-06-06	1 minute
datetime	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 through 9999-12-31	0.333 second
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999	100 nanoseconds
datetimeoffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+-]hh:mm	00001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)	100 nanoseconds

Source: http://blogs.msdn.com/usisvde/archive/2007/11/05/get-a-date.aspx

Reminder: TSQL date only

Get the date portion of datetime, correctly:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))


since,

CONVERT(VARCHAR(20),GETDATE(),112) returns the wrong date sometimes.

-----A cool and faster way:


DECLARE @datemask bigint
DECLARE @timemask bigint

SET @datemask = 0xffffffffff000000
SET @timemask = 0x0000000000ffffff

SELECT CAST(CAST((CAST(getdate() as binary) & @datemask) as binary(8)) as datetime)



//IF you need to grab a date and a time from two different locations in the database and combine them into one datetime (ex: shifts(time) and payperiod(date)):

SELECT CAST(CAST((CAST(getdate() as binary) & (@datemask | (CAST((CAST(getdate() as binary) & (@timemask) ) as binary(8)))) ) as binary(8)) as datetime)