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:
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.
|S/No||Data Type||Storage (bytes)||Date Range||Accuracy||Entry Format|
|1||DATETIME||8||01-Jan-1753 to 31-Dec-9999||3.333 msecs||‘YYYYMMDD hh:mm:ss.nnn’|
|2||SMALLDATETIME||4||01-Jan-1900 to 06-Jun-2079||1 min||‘YYYYMMDD hh:mm’|
|3||DATE||3||01-Jan-0001 to 31-Dec-9999||100 nsecs||‘YYYY-MM-DD’|
|4||TIME||3 to 5||N/A||100 nsecs||‘hh:mm:ss.nnnnnnn’|
|5||DATETIME2||6 to 8||01-Jan-0001 to 31-Dec-9999||100 nsecs||‘YYYYMMDD hh:mm:ss.nnnnnnn’|
|6||DATETIMEOFFSET||8 to 10||01-Jan-0001 to 31-Dec-9999||100 nsecs||‘YYYYMMDD hh:mm:ss.nnnnnnn[+|-] hh:mm’|
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.
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' )
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' )
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
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' )
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.
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.
- SQL Data Types
- Ben-Gan, I. (2016) T-SQL Fundamentals. pp74-78. Microsoft Press.