Using DATE, TIME, and DATETIME2 in Font

Printer PDF-417 2d barcode in Font Using DATE, TIME, and DATETIME2

You can store just a date or time value by using the new DATE and TIME data types. The traditional DATETIME and SMALLDATETIME data types default to 12 a.m. when you do not specify the time. You can also specify a precision, from zero to seven decimal places, when using the TIME and DATETIME2 data types. Type in and execute Listing 9-6 to learn how to use the new types.

Listing 9-6. Using DATE and TIME USE tempdb; --1 IF OBJECT_ID('dbo.DateDemo') IS NOT NULL BEGIN DROP TABLE dbo.DateDemo; END GO --2 CREATE TABLE dbo.DateDemo(JustTheDate DATE, JustTheTime TIME(1), NewDateTime2 DATETIME2(3), UTCDate DATETIME2); GO --3 INSERT INTO dbo.DateDemo (JustTheDate, JustTheTime, NewDateTime2, UTCDate) VALUES (SYSDATETIME(), SYSDATETIME(), SYSDATETIME(), SYSUTCDATETIME()); --4 SELECT JustTheDate, JustTheTime, NewDateTime2, UTCDate FROM dbo.DateDemo; Figure 9-9 shows the results. Code section 1 drops the dbo.DateDemo table if it already exists. Statement 2 creates the dbo.DateDemo table with a DATE, a TIME, and two DATETIME2 columns. Notice that the TIME and DATETIME2 columns have the precision specified. The default is seven places if a precision is not specified. Statement 3 inserts a row into the table using the new SYSDATETIME function. This function works like the GETDATE function except that it has greater precision than GETDATE. The statement populates the UTCDate column with the SYSUTCDATETIME function, which provides the Coordinated Universal Time (UTC). Statement 4 shows the results. The JustTheDate value shows that even though the SYSDATETIME function populated it, it stored only the date. The JustTheTime values stored only the time with one decimal place past the seconds. The NewDateTime2 column stored both the date and time with three decimal places. The UTCDate column stored the UTC date along with seven decimal places. Since the computer running this demo is in Central time, the time is five hours different.

One issue I have seen with these new data types is that running SQL Server on Vista 64-bit does not record all the decimal places with the new data types and fills in with zeros after three places. You will see better results when running SQL Server on different operating systems. Most business applications will not require the default precision of seven places found with the TIME and DATETIME2 types. Be sure to specify the required precision when creating tables with columns of these types to save space in your database.

The new DATETIMEOFFSET data type contains, in addition to the date and time, a time zone offset for working with dates and times in different time zones. This is the difference between the UTC date and time and the stored date. Along with the new data type, several new functions for working with DATETIMEOFFSET are available. Type in and execute Listing 9-7 to learn how to work with this new data type. Listing 9-7. Using the DATETIMEOFFSET Data Type USE tempdb; --1 IF OBJECT_ID('dbo.OffsetDemo') IS NOT NULL BEGIN DROP TABLE dbo.OffsetDemo; END; --2 CREATE TABLE dbo.OffsetDemo(Date1 DATETIMEOFFSET); GO --3 INSERT INTO dbo.OffsetDemo(Date1) VALUES (SYSDATETIMEOFFSET()), (SWITCHOFFSET(SYSDATETIMEOFFSET(),'+00:00')), (TODATETIMEOFFSET(SYSDATETIME(),'+05:00'))

--4 SELECT Date1 FROM dbo.OffsetDemo; Figure 9-10 shows the results. Code section 1 drops the dbo.OffsetDemo table if it exists. Statement 2 creates the table with a DATETIMEOFFSET column, Date1. Statement 3 inserts three rows into the table using the new functions for working with the new data types. The SYSDATETIMEOFFSET function returns the date and time on the server along with the time zone offset. The computer I am using is five hours behind UTC, so the value 05:00 appears after the current date and time. Using the SWITCHOFFSET function, you can switch a DATETIMEOFFSET value to another time zone. Notice that by switching to