Introduction

Data types are attributes that specify the kind of data that objects such as columns, local variables, expressions, and parameters can hold. Across the RDBMS world, data types are typically grouped into string, numeric, and date data types.

T-SQL supports 6 date and time data types namely:

1. Datetime
2. Smalldatetime
3. Date
4. Time
5. Datetime2
6. Datetimeoffset

The first two data types are considered as legacy versions of the newer ones. In this article, we focus on the date data types and, specifically, on the datetime and datetime2 data types available in SQL Server. Table 1 gives details of the various date and time data types available in SQL Server.

[table id=59 /]

Tab 1 Date and Time Data Types

Datetime and Datetime2

Datatime is a datatype that combines date with time in a 24-hour clock format. The date range supported in the datetime data type is as shown in Tab 1 and it has an accuracy of about 3 milliseconds.

Datetime2 is an extension of the datetime data type. It accommodates a wider range of possible values and has an accuracy of 100 nanoseconds which is much better than its predecessor. Another key aspect of the dattime2 data type is that the storage required ranges from 6 to 8 bytes depending on the precision you choose.

• You can achieve a precision of 1 millisecond by allowing three decimal places on the seconds component. Each value will thus consume six bytes.
• You can achieve a precision of 100 nanoseconds by allowing seven decimal places on the seconds component. Each value will thus consume eight bytes.

Demonstrations

Insert Wrong Date Values

We create a table with the details shown in Listing 1 to carry out a few demonstrations which illustrate how to manipulate the datetime and datetime2 data types.

-- Listing 1 Create Table and insert Rows
-- Create Table with Data Types
use Practice2017
go
create table staffers (
fname varchar(50),
lname varchar(50),
JobTitle varchar(100),
DOB datetime,
PreciseDOB datetime2,
LastLoginTime time)
go

Then we try to populate the table with one row as shown in Listing 2 but we get the error shown in Fig. 1. The key word in the error message is “out-of-range” values. What it is saying is that her value we are trying to insert is either lower than 01-Jan-1753 or higher than 31-Dec-9999. In this case, the problem is that we have not used the recommended entry format of ‘YYYYMMDD hh:mm:ss.nnn’ (see Table 1). Reading the value ‘06101979‘, SQL Server assumes 0610 to be the year (matching YYYY). This error is not thrown for the datetime2 data type because the range for datetime2 is wider starting in the year 0001.

-- Listing 2 Insert Rows with Wrong Entry Format
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'06101979'
,'06101979'
,'8:00 AM'
)

Fig. 1 Error Returned for Datetime Column

Insert Correct Date Values

We try to correct the problem by entering the correct entry format for the datetime column as shown in Listing 3. When we run the statement again, we get the error shown in Fig. 2. This error essentially is caused by the same failure to follow the Entry format specifications. However, the problem lies with the other part of the date ‘06101979‘ which matches with the entry format ‘YYYYMMDD hh:mm:ss.nnn’. In this case, SQL Server assumed 19 Is a month and 79 is a day of the month. Attempting this implicit conversion fails since neither of the preceding assertions is true.

-- Listing 3 Insert Rows with One Correct Entry Format
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'19791006'
,'01061979'
,'8:00 AM'
)

Fig. 2 Error Returned for Datetime2 Column

Listing 4 allows us to demonstrate the last assertion. The value 01101201 fits into the range for datetime2 and we are able to insert the row. This value translates to 1st December 0110 as we see in fig 3.

-- Listing 4: Insert Rows with Correct Date Format
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'19791006'
,'01101201'
,'8:00 AM')

-- Listing 5: Insert Rows with All Correct Entry Format
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'19791006'
,'19791006'
,'8:00 AM'
)

Verifying the Data

Fig. 3 Querying the Data Set

When we query the staffers table we see clearly the precision of the datetime data type when compared to the datetime2 alternative. Let’s move on to something a little more sinister: Language settings. Take a look at Listing 6. We are inserting the exact same records using the date format 06/10/1979. This format is NOT language neutral thus when we set language to British in the first statement and then to us_english in the second we find that we have inserted two different dates actually though our raw values are the same. This is why it is so important to always use the recommended entry format when dealing with datetime and datetime2.

-- Listing 6: Impact of Language Settings

set language british
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'06/10/1979'
,'06/10/1979'
,'8:00 AM'
)


set language us_english
insert into staffers 
values
(
'Kenneth'
,'Igiri'
,'Database Administrator'
,'06/10/1979'
,'06/10/1979'
,'8:00 AM'
)

Fig. 4 Querying Staffers

With the language setting as British, SQL Server interprets the first two figures as the day but with the language setting as us_english, SQL Server interprets the first two figures as a month. One last thing that we need to mention here is that in inserting our records, we did not specify the time component thus SQL Server automatically assumes were mean midnight of the specified date.

Conclusion

In this article, we have learned what the datetime and datetime2 data types look like, their key differences and how to make sure you are entering the correct date when using these data types. In the course of it, we have also examined two errors which a developer could run into when working with these data types.

References

• SQL Data Types
• Ben-Gan, I. (2016) T-SQL Fundamentals. pp74-78. Microsoft Press.