Experimenting with Date and Time data types in SQL SERVER 2008

Introduction

The following information about Date and Time types for SQL SERVER 2008 R2 is given in the official Microsoft MSDN.

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.

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.

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.

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