Home » Microsoft » MB6-886 » Which data type should you use?
You are a database developer on an instance of SQL Server 2008. You need to add a column to your AssemblyDetail table to hold a date and time value. You want the time portion to include to highest data precision possible, but you do not want to store time zone information.
Which data type should you use?
A. smalldatetime
B. datetimeoffset
C. datetime2
D. datetime
Correct Answer: C
Explanation/Reference:
You should use a datetime2 data type. The datetime2 data type is a new SQL Server 2008 data type that is similar to a datetime data type, but has greater range and precision. The datetime2 data type stores a date and a time that includes fractional seconds up to seven digits of precision (YYYY-MM-DD hh:mm:ss.0000000).
You should not use a smalldatetime data type. The smalldatetime data type stores both the date and the time, without including any fractional seconds.
You should not use a datetimeoffset data type because in this scenario you did not want to store time zone information. The datetimeoffset data type includes time zone information as 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.
You should not use a datetime data type because a datetime2 data type has greater range and precision. A datetime data type has only three digits to represent fractional seconds of the time.