You are creating an application that will be used by several divisions of your company to record customer survey information. Divisions of the company are located in different time zones throughout the world. In the SurveyDate column of the Survey table, you want to store the date and time each survey was completed, including time zone information.
You also want to record the amount of time each respondent took to complete the survey in the ElapsedTime column in hh:mm:ss format.
Which CREATE TABLE statement should you use?
A. CREATE TABLE Survey (
SurveyID int PRIMARY KEY,SurveyDate datetimeoffset,ElapsedTime time);
B. CREATE TABLE Survey (
SurveyID int PRIMARY KEY,SurveyDate datetime2,ElapsedTime time);
C. CREATE TABLE Survey (
SurveyID int PRIMARY KEY,SurveyDate datetimeoffset,ElapsedTime time(4));
D. CREATE TABLE Survey (
SurveyID int PRIMARY KEY,SurveyDate datetime2,ElapsedTime time(4));
Correct Answer: A
Explanation/Reference:
SQL Server 2008 introduces four new date and time data types: datetimeoffset, time, date, and datetime2. The datetimeoffset data type includes time zone information in the form of a time zone offset. The time zone offset, which is in the format of +hh:mm or -hh:mm, ranges from 14:00 to +14:00. The time zone offset indicates the difference between Coordinated Universal Time (UTC) and the local time. For a positive time zone offset, the local time is calculated by adding the time zone offset to UTC. For a negative time zone offset, the local time is calculated by subtracting the time zone offset from UTC. For example, a time zone offset of -11:00 indicates that the date/time uses a local time zone that is eleven hours behind UTC. The time data type allows you to store only a time, including hours, minutes, seconds, and optionally nanoseconds. The date data type allows you to store only a date, and the datetime2 data type is similar to the datetime data type but has greater range and precision.
In this scenario, you wanted to store the survey date to include the time zone information, so you should define the SurveyDate column as a datetimeoffset data type. The datetimeoffset data type is the only data type that is time-zone aware. You also wanted to define theElapsedTime column to include only a time. To accomplish this, you would define the ElapsedTime column as a time data type. A time data type stores only a time in hh:mm:ss[n] format. A precision can be specified to store fractional seconds. In this scenario, no fractional seconds were required, so the default precision can be used.
You should not use the statement that defines the SurveyDate column as a datetime data type and the ElapsedTime column as a smalldatetime data type. The datetime data type is not time-zone aware as required in this scenario. Also, only the time portion is required for the ElapsedTime column in this scenario; therefore, a time data type should be used. The smalldatetime data type stores both the date and the time with the time not including any fractional seconds.
You should not use the statement that defines the SurveyDate column as a datetime2 data type. The datetime2 data type is not time-zone aware, as required in this scenario.
You should not use the statement that defines the ElapsedTime column using time(4). In this scenario, you only needed to store the time in hh:mm:ss format without any fractional seconds. Therefore, specifying a precision is not required.
