SQL DateTypes

CREATE TABLE ##table1 (
    Id INT
    , dateOne DATETIME -- will store miliseconds
    , dateTwo DATETIME2 -- will store nanoseconds
    , dateThree DATETIME2(0) -- cuts off past the second
    , dateFour DATETIME2 -- can see getDate() is less precision than sysDateTime()
    , dateFive DATE -- new date type, stores only the date part
    , timeOne TIME -- new time type, stores only the time part down to nanoseconds
    , timeTwo TIME(0) -- time type storing only down to the seconds
);
INSERT INTO ##table1 (
    Id
    , dateOne
    , dateTwo
    , dateThree
    , dateFour
    , dateFive
    , timeOne
    , timeTwo
)
VALUES (
    1
    , sysdatetime() -- DATETIME - 2015-04-03 14:06:46.323
    , sysdatetime() -- DATETIME2 - 2015-04-03 14:06:46.3235366
    , sysdatetime() -- DATETIME2(0) - 2015-04-03 14:06:46
    , getdate() -- DATETIME2 - 2015-04-03 14:06:46.3170000
    , sysdatetime() -- DATE - 2015-04-03
    , sysdatetime() -- TIME - 14:06:46.3235366
    , sysdatetime() -- TIME(0) - 14:06:46
)

SELECT * FROM ##table1

DROP TABLE ##table1