Introduction
The following information about Date and Time types for SQL SERVER 2008 R2 is given in the official Microsoft MSDN.
| Data type | Format | Range | Accuracy |
| time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 through 23:59:59.9999999 | 100 nanoseconds |
| date | YYYY-MM-DD | 0001-01-01 through 9999-12-31 | 1 day |
| smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01 through 2079-06-06 | 1 minute |
| datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 through 9999-12-31 | 0.00333 second |
| datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 | 100 nanoseconds |
| datetimeoffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) | 100 nanoseconds |
The source for the above information is the following link.
https://msdn.microsoft.com/en-gb/library/ms186724(v=sql.105).aspx
And if you click on the data type hyperlink, it will take you to more details of the particular type. In the details page each data type has a default value. For example datetime type has the default value of 1900-01-01 00:00:00
To understand what these mean, how they are represented and handled in SQL server and how developers, business analysts and technical architects perceive them in the real world, I am going to embark on an experiment that will test and unravel each type of date and time. This will enable us understand better, see them clearer and apply them efficiently in our applications.
To recap, I am going to experiment the following characteristics of each date and time type of SQL SERVER 2008 R2.
- Default Value
- Format
- Accuracy
- Range
Note: Although this experiment was carried out only in SQL SERVER 2008 R2, it shouldn’t be any different in SQL SERVER 2012 and 2014. But I cannot confirm though.
Default Value
Firstly I am going to experiment the default values for date and time. The following values are taken from the MSDN link given above.
- Time: 00:00:00
- Date: 1900-01-01
- SmallDateTime: 1900-01-01 00:00:00
- DateTime: 1900-01-01 00:00:00
- DateTime2: 1900-01-01 00:00:00
- DateTimeOffset: 1900-01-01 00:00:00 00:00
Now, the question is, what do they mean by default values? Normally the developer assigns the default values for columns so where do they take the default value from and when are they used? Only way of finding out is creating a table and inserting a row. I am going to create a table with columns of each type using the following script
CREATE TABLE FunWithDateAndTime
(
DateColumn date,
TimeColumn time,
SmallDateTimeColumn smalldatetime,
DateTimeColumn datetime,
DateTime2Column datetime2,
DateTimeOffsetColumn datetimeoffset
)
The table is create and now I am going to insert a row with providing only value for DateColumn and expecting default values for other columns.
INSERT into FunWithDateAndTime(DateColumn) VALUES(GETDATE())
(1 row(s) affected)
Let’s check the values that are inserted now
SELECT * FROM FunWithDateAndTime
DateColumn TimeColumn SmallDateTimeColumn DateTimeColumn DateTime2Column DateTimeOffsetColumn
2015-03-08 NULL NULL NULL NULL NULL
Well, as you can see the default values are NULL because the columns are nullable in DDL (Table Script). If I have defined these columns as “Not Null” in the DDL then when I need to insert the default values myself that I could choose any valid date or time. So where are the default values used.
Actually the default values are used during conversion from one type to another. Let me experiment with some examples.
Type: Time
SELECT CAST('2015-03-08' as time)
00:00:00.0000000
SELECT CAST('' as time)
00:00:00.0000000
Type: Date
SELECT CAST('14:12:10' as date)
1900-01-01
SELECT CAST('' as date)
1900-01-01
Type: SmallDateTime
SELECT CAST('14:12:10' as smalldatetime)
1900-01-01 14:12:00
SELECT CAST('2015-03-10' as smalldatetime)
2015-03-10 00:00:00
SELECT CAST('' as smalldatetime)
1900-01-01 00:00:00
Type: DateTime
SELECT CAST('2015-03-10' as datetime)
2015-03-10 00:00:00.000
SELECT CAST('10:13:10' as datetime)
1900-01-01 10:13:10.000
SELECT CAST('' as datetime)
1900-01-01 00:00:00.000
Type: DateTime2
SELECT CAST('' as datetime2)
1900-01-01 00:00:00.0000000
SELECT CAST('2015-03-09' as datetime2)
2015-03-09 00:00:00.0000000
SELECT CAST('15:20:15' as datetime2)
1900-01-01 15:20:15.0000000
Type: DateTimeOffset
SELECT CAST('15:20:15' as datetimeoffset)
1900-01-01 15:20:15.0000000 +00:00
SELECT CAST('' as datetimeoffset)
1900-01-01 00:00:00.0000000 +00:00
SELECT CAST('2015-03-10' as datetimeoffset)
2015-03-10 00:00:00.0000000 +00:00
As you could clearly see, the default values are used for whichever part of the type is missed. For example when a time part is missed the default 00:00:00 is being used, and when a date part is missed the default 1900-01-01 is being used.
Format
Secondly I am going to experiment the format for each type. To experiment this I am going to insert a row with some values
INSERT INTO FunWithDateAndTime(DateColumn,
TimeColumn,
SmallDateTimeColumn,
DateTimeColumn,
DateTime2Column,
DateTimeOffsetColumn)
VALUES (GETDATE(),
GETDATE(),
SYSDATETIME(),
GETDATE(),
SYSDATETIME(),
SYSDATETIME())
Now, I am going to check the format of each type by selecting the values from the record that I just inserted.
SELECT * FROM FunWithDateAndTime
The result is
Let’s verify them now. The actual formats are matching with the expected formats.
| Column | Data Type | Expected Format | Actual Value | Actual Format |
| DateColumn | Date | YYYY-MM-DD | 2015-03-04 | YYYY-MM-DD |
| TimeColumn | Time | hh:mm:ss.nnnnnnn | 11:35:37.9470000 | hh:mm:ss.nnnnnnn |
| SmallDateTimeColumn | SmallDateTime | YYYY-MM-DD hh:mm:ss | 2015-03-04 11:36:00 | YYYY-MM-DD hh:mm:ss |
| DateTimeColumn | DateTime | YYYY-MM-DD hh:mm:ss.nnn | 2015-03-04 11:35:37.947 | YYYY-MM-DD hh:mm:ss.nnn |
| DateTime2Column | DateTime2 | YYYY-MM-DD hh:mm:ss.nnnnnnn | 2015-03-04 11:35:37.9611297 | YYYY-MM-DD hh:mm:ss.nnnnnnn |
| DateTimeOffsetColumn | DateTimeOffset | YYYY-MM-DD hh:mm:ss.nnnnnnn [+/-] hh:mm | 2015-03-04 11:35:37.9611297 +00:00 | YYYY-MM-DD hh:mm:ss.nnnnnnn + hh:mm |
Accuracy
Thirdly I am going to experiment the accuracy for each type for date and time.
Date Type : Accuracy = 1 day
As we know the date type can hold only a date the maximum accuracy we can achieve is a day.
What it means is that if we have to have accuracy less than a day then we cannot use date type. Suppose an application needs to store date and time information in hours, then date can achieve only maximum accuracy 24 hours not less than that.
Let me show you an example,
An application need to calculate the time taken by a vehicle for a journey from place A to place B on a particular day. First vehicle start time = ‘2014-05-04 13:40:15’ end time = ‘2014-05-05 14:40:15’
So the time taken is 1 day and 1 hour. If I calculate this using date type, then I will get only 1 day and lose 1 hour. You can see the result below. Because date type doesn’t hold time it is not possible to get hour.
SELECT datediff(hh,cast('2014-05-04 13:40:15' as date),cast('2014-05-05 14:40:15' as date))
24
SELECT datediff(d,cast('2014-05-04 13:40:15' as date),cast('2014-05-05 14:40:15' as date))
1
Time Type : Accuracy = 100 nanoseconds
Let me remind you the format of time type first which is hh:mm:ss.nnnnnnn
Now let me compare two F1 cars lap times and find the gap between them. The gap is 130 ns
CAR 1: 00:01:23.456814250
CAR 2: 00:01:23.456814380
But if use time type for this calculation, then I will get only 100ns because the time type can give you the maximum accuracy 100 ns. You can see the result below
SELECT datediff(ns, cast('00:01:23.456814250' as time), cast('00:01:23.456814380' as time))
100
Tip: This is simply because the time can hold maximum of 7 digits for fractional seconds that truncates the nano seconds
SmallDateTime Type : Accuracy = 1 minute
To experiment SmallDateTime I am going to run some SQL and show you the results.
SELECT CAST (CAST('2015-05-05 12:10:49' as smalldatetime) - CAST('2015-05-05 12:10:28' as smalldatetime) as TIME)
00:01:00.0000000
I was expecting 21 seconds not 1 minute. How did we get 1 minute. Let me explain. According to MSDN article , for smalldatetype types any values that are 29.998 seconds or less are rounded down to the nearest minut and any values of 29.999 seconds or more are rounded up to the nearest minute.
Therefore, 12:10:49 becomes 12:11:00 and 12:10:28 becomes 12:10:00, and the result becomes 12:11:00 – 12:10:00 = 00:01:00
Now let me show you another example with different values.
SELECT CAST (CAST('2015-05-05 12:10:30' as smalldatetime) - CAST('2015-05-05 12:10:58' as smalldatetime) as TIME)
00:00:00.0000000
As I have explained before, 12:10:30 becomes 12:11:00 and 12:10:58 becomes 12:10:11 thus the result is 12:11:00 – 12:11:00 = 00:00:00
DateTime Type : Accuracy = Rounded to increments of .000, .003, or .007 seconds
The accuracy for datetime is rounded to increments of .000, .003 or .007 seconds. Let me explain with some examples.
SELECT CAST(CAST('2015-05-05 12:10:49.801' as datetime) - CAST('2015-05-05 12:10:49.800' as datetime) as datetime)
1900-01-01 00:00:00.000
I was expecting 801 – 800 = 1ns = .001s. But as we know if any values is between .000s and .003s will be rounded to either .000s or .003s. Because .001s is nearest to .000s it is rounded to .000s
Let me show you another example,
SELECT CAST(CAST('2015-05-05 12:10:49.802' as datetime) - CAST('2015-05-05 12:10:49.800' as datetime) as datetime)
1900-01-01 00:00:00.003
In the above example I was expecting .0002s and as we know it is rounded to .003s as increments.
I have given below some more examples for your understanding.
SELECT CAST(CAST('2015-05-05 12:10:49.804' as datetime) - CAST('2015-05-05 12:10:49.800' as datetime) as datetime)
1900-01-01 00:00:00.003
SELECT CAST(CAST('2015-05-05 12:10:49.807' as datetime) - CAST('2015-05-05 12:10:49.800' as datetime) as datetime)
1900-01-01 00:00:00.007
SELECT CAST(CAST('2015-05-05 12:10:49.809' as datetime) - CAST('2015-05-05 12:10:49.800' as datetime) as datetime)
1900-01-01 00:00:00.010
In the above example the actual result .009s is rounded to .010s which is in increments of .000s, .003s or .007s.
DateTime2 Type : Accuracy = 100 nanoseconds
Format is YYYY-MM-DD hh:mm:ss.nnnnnnn
Please refer to Time type for explanation.
DateTimeOffset Type : Accuracy = 100 nanoseconds
Format is YYYY-MM-DD hh:mm:ss.nnnnnnn [+/-] hh:mm
Please refer to Time type for explanation.
Range
And finally I am going to experiment the range for each type. To carry out this test I am going to insert a series of values into each column and see what values are allowed.
Date Type : Range = 0001-01-01 through 9999-12-31
INSERT INTO FunWithDateAndTime(DateColumn) VALUES ('0000-00-00')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 0000 - invalid year, 00 – invalid month, 00-invalid day
INSERT INTO FunWithDateAndTime(DateColumn) VALUES ('0001-01-01')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(DateColumn) VALUES ('0001-13-01')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 13 – Invalid month
INSERT INTO FunWithDateAndTime(DateColumn) VALUES ('0001-03-32')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 32 – Invalid day
INSERT INTO FunWithDateAndTime(DateColumn) VALUES ('0100-03-30')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(DateColumn) VALUES ('9999-12-31')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(DateColumn) VALUES ('10000-12-31')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 10000 – Invalid year
Time Type : Range = 00:00:00.0000000 through 23:59:59.9999999
INSERT INTO FunWithDateAndTime(TimeColumn) VALUES ('-1:00:00.0000000')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: -1 – Invalid hour
INSERT INTO FunWithDateAndTime(TimeColumn) VALUES ('00:00:00.0000000')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(TimeColumn) VALUES ('01:01:01.0000001')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(TimeColumn) VALUES ('23:59:59.9999999')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(TimeColumn) VALUES ('23:59:60.9999999')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 60 – Invalid seconds
INSERT INTO FunWithDateAndTime(TimeColumn) VALUES ('23:60:59.9999999')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 60 – Invalid minutes
INSERT INTO FunWithDateAndTime(TimeColumn) VALUES ('24:59:59.9999999')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 24 – Invalid hours
INSERT INTO FunWithDateAndTime(TimeColumn) VALUES ('23:59:59:10000000')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 60 – Invalid fractional seconds
SmallDateTime Type : Range = 1900-01-01 through 2079-06-06 and 00:00:00 through 23:59:59
Tip:
The maxmimum limit for smalldatetime is 2079-06-06.
If you wonder why , it is because the smalldatetime storage size is 4 bytes.
2 byes are used for date and 2 bytes are used for time.
So the maximum number can be stored in 2 bytes is 65535 and smalldatetime stores the dates in terms of the days since 1900 which is 65535.
SELECT DATEDIFF(day,'1900-01-01','2079-06-06') = > 65535
INSERT INTO FunWithDateAndTime(SmallDateTimeColumn) VALUES ('1900-01-01 23:59:59')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(SmallDateTimeColumn) VALUES ('2080-01-01 23:59:59')
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
The statement has been terminated
Reason: 2080 – Invalid year (out of range)
INSERT INTO FunWithDateAndTime(SmallDateTimeColumn) VALUES ('2079-07-01 23:59:59')
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
The statement has been terminated.
Reason: 07 – Invalid month for year 2079 (out of range)
INSERT INTO FunWithDateAndTime(SmallDateTimeColumn) VALUES ('2079-06-07 23:59:59')
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
The statement has been terminated.
Reason: 07 – Invalid day for month 06 and year 2079 (out of range)
DateTime Type : Range = 1753-01-01 through 9999-12-31 and 00:00:00 through 23:59:59.997
INSERT INTO FunWithDateAndTime(DateTimeColumn) VALUES ('1752-01-01 23:59:59')
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Reason: 1752 – Invalid year
INSERT INTO FunWithDateAndTime(DateTimeColumn) VALUES ('1753-06-06 23:59:59')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(DateTimeColumn) VALUES ('9999-12-31 23:59:59')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(DateTimeColumn) VALUES ('18888-12-31 23:59:59')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 1888 – Invalid year
DateTime2 Type : Range = 0001-01-01 through 9999-12-31 and 00:00:00 through 23:59:59.9999999
INSERT INTO FunWithDateAndTime(DateTime2Column) VALUES ('0001-01-01 23:59:59.9999999')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(DateTime2Column) VALUES ('0000-01-01 23:59:59.9999999')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 0000 – Invalid year
INSERT INTO FunWithDateAndTime(DateTime2Column) VALUES ('9999-99-99 23:59:59.9999999')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 99 – Invalid month and day
DateTimeOffset Type : Range
Date and Time = 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)
Time Zone = -14:00 through +14:00
INSERT INTO FunWithDateAndTime(DateTimeOffsetColumn) VALUES ('0001-01-01 23:59:59.9999999+12:00')
(1 row(s) affected)
INSERT INTO FunWithDateAndTime(DateTimeOffsetColumn) VALUES ('0001-01-01 23:59:59.9999999+15:00')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: +15:00 – Invalid time zone (out of range)
INSERT INTO FunWithDateAndTime(DateTimeOffsetColumn) VALUES ('0001-01-01 23:60:59.9999999+14:00')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Reason: 60 – Invalid minutes
In the above examples I have tested some values outside and inside the range, and I have given the reason for failed inserts.
I hope you found this article useful and it helped you understand the date and time types better.
You could download the scripts used in this article here
