Using the New Date and Time Data Types

1.       The new date and time data types in SQL Server 2008 offer increased range and precision and are ANSI SQL compatible.

2.       Separate date and time data types minimize storage space requirements for applications that need only date or time information. Moreover, the variable precision of the new time data type increases storage savings in exchange for reduced accuracy.

3.       The new data types are mostly compatible with the original date and time data types and use the same Transact-SQL functions.

4.       The datetimeoffset data type allows you to handle date and time information in global applications that use data that originates from different time zones.

SELECT c.name, p.* FROM politics p
JOIN country c
ON p.country = c.code
WHERE YEAR(Independence) < 1753
ORDER BY Independence
GO
8.    Highlight the SELECT statement and click Execute ( ) to show the use of some of the date functions.
T-SQL
SELECT c.name AS [Country Name],
       CONVERT(VARCHAR(12), p.Independence, 107)
AS [Independence Date],
       DATEDIFF(YEAR, p.Independence, GETDATE())
AS [Years Independent (appox)],
       p.Government
FROM politics p
JOIN country c
ON p.country = c.code
WHERE YEAR(Independence) < 1753
ORDER BY Independence
GO

10.    Select the SET DATEFORMAT statement and click Execute ( ) to change the DATEFORMAT to day-month-year.
T-SQL
SET DATEFORMAT dmy
GO
11.    Select the DECLARE and SELECT statements and click Execute ( ) to show how the datetime and datetime2 data types interpret a date literal.
T-SQL
SET DATEFORMAT dmy
DECLARE @dt datetime = '2008-12-05'
DECLARE @dt2 datetime2 = '2008-12-05'
SELECT MONTH(@dt) AS [Month-Datetime], DAY(@dt)
    AS [Day-Datetime]
SELECT MONTH(@dt2) AS [Month-Datetime2], DAY(@dt2)
    AS [Day-Datetime2]
GO
12.    Highlight the DECLARE and SELECT statements and click Execute ( ) to use integer arithmetic on a datetime variable.
T-SQL
DECLARE @dt datetime = '2008-12-05'
SELECT @dt + 1
GO

13.    Highlight the DECLARE and SELECT statements and click Execute ( ) to show how integer arithmetic is not allowed for datetime2 variables.
T-SQL
DECLARE @dt2 datetime = '2008-12-05'
SELECT @dt2 + 1
GO
14.    Highlight the DECLARE and SELECT statements and click Execute ( ) to show how to use DATE functions to do simple arithmetic on datetime2 variables.
T-SQL
DECLARE @dt2 datetime2(7) = '2008-12-05'
SELECT DATEADD(d, 1, @dt2)
GO
15.    Highlight the DECLARE and SELECT statements and click Execute ( ) to show how the GETDATE function can be used with both datetime and datetime2 data types.
T-SQL
DECLARE @dt datetime = GETDATE();
DECLARE @dt2 datetime2(7) = GETDATE();
SELECT @dt AS [GetDate-DateTime], @dt2 AS [GetDate-DateTime2]
GO
16.    Draw attention to the values returned for both columns and how they are equal.
17.    Highlight the DECLARE and SELECT statements and click Execute ( ) to show how the SYSDATETIME function can be used with both datetime and datetime2 data types.
T-SQL
DECLARE @dt datetime = SYSDATETIME();
DECLARE @dt2 datetime2(7) = SYSDATETIME();
SELECT @dt AS [Sysdatetime-DateTime], @dt2
    AS [Sysdatetime-DateTime2]
GO
18.    Draw attention to the values returned for both columns and how they are different.

Programming Global Applications with DateTimeOffset

2.    If you have not previously created the SQLTrainingKitDB database while completing another demo in this training kit, highlight the CREATE DATABASE statement and click Execute ( ) to do so now.
T-SQL
CREATE DATABASE SQLTrainingKitDB
GO
3.    Select the USE and CREATE TABLE statements and click Execute ( ) to create table datetest in the SQLTrainingKitDB database.
T-SQL
USE SQLTrainingKitDB
GO

CREATE TABLE datetest (
  id integer IDENTITY PRIMARY KEY,
  datetimecol datetimeoffset,
  EnteredTZ varchar(40)
);

 Reference:http://www.microsoft.com/downloads/details.aspx?FamilyID=E9C68E1B-1E0E-4299-B498-6AB3CA72A6D7&displaylang=en