Difference between DateTime and DateTime2 in SQL Server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 129 of sequel server
tutorial in this video we'll discuss the
difference between date time and date
time to data types in sequel server here
are the differences the first difference
is from the date range perspective for
date time it is January 1st 1753 to
December 31st 9999 for day time to
notice we have caught a broader date
range
it starts from January 1st 0 0 0 1 to
December 31st 9999 even from a time
range perspective notice we have caught
better precision with date/time - than
date time so for daytime the time range
starts from 0 / 0 minutes 0 seconds to
23 hours 59 minutes 59 seconds and 997
million seconds whereas for daytime - it
is 0 or 0 minute 0 seconds - 23 hours 59
minutes 59 seconds and look at the
milliseconds with milliseconds we have
got seven digits precision which means
date time - is going to be much more
accurate than daytime so as far as the
accuracy is concerned with day time the
accuracy is up to three point three
three milliseconds whereas for daytime -
it is 100 nanoseconds look at the unit
of measurement date/time is in
milliseconds whereas date/time - is in
nanoseconds which means
date/time - provides better accuracy
than date/time and if you look at the
storage size it's fixed for daytime
eight pi it's for daytime - the storage
size varies between 6 to 8 bytes
depending on the precision that we pick
we'll look at an example of how the
storage size changes with the precision
that we choose in just a bit as far as
the default value is concerned it's the
same for both the data types
it starts from january first nineteen
hundred year over zero minute zero
seconds so from these differences it's
very clear that date/time - has got a
broader date range than daytime and also
date/time - provides better accuracy
than date/time so I would personally
recommend using date/time - over
date/time datatype whenever possible I
think the only reason for using data
over daytime - datatype is for backward
compatibility other than that I don't
see any other compelling reason for
using daytime over daytime - datatype
now let's look at the syntax for
date/time - so first we specified a time
- and then we can optionally specify
fractional seconds precision if we don't
provide a value for this parameter
explicitly then the default precision is
going to be seven digits
the precision scale is from zero to
seven digits depending on the precision
that you choose depending on your
application needs the storage size is
going to change and here is that table
notice for precision zero one and two
the storage size is six for precision
three and for storage size is seven for
precision five six and seven storage
size is eight bytes let's look at this
in action
what I'm going to do now is declare a
table variable let's call it temp table
and this table variable is going to
contain eight columns okay and all eight
columns data type will be daytime and
for each column the position is going to
change from zero to seven okay so I'm
going to create a column let's call it
daytime - and we want precision zero
okay so date time - and I'm going to
explicitly specify a value for this
parameter fractional seconds precision
so I want a precision of zero okay and
similarly I'm going to create another
column but this time I want a precision
of one so we are explicitly specifying
the precision that we want similarly I
want to create in our six more columns
all the way till precision seven in the
interest of time I have already typed
the code so I am going to copy it and
paste it right here
so we have precision to precision three
and precision for all the way till
precision seven and look at the value
for precision parameter so that's our
table variable and now what I'm going to
do is insert values for all these
columns so insert into and our table
variable is at temp table values and I'm
going to supply values for each of these
columns and I'm going to supply the same
value 20th of October 2015 15 hours nine
minutes 12 seconds and I'm going to you
know provide one two three four five six
seven you know the value for
milliseconds so 2015 October 20th and
the time is going to be fifteen hours
nine minutes 12 seconds
so 15 hours nine minutes 12 seconds and
then one two three four five six seven
okay seven digits and I'm going to do
that you know eight times because we
have eight columns here we have to
populate all those columns so I'm going
to paste this so eight times all right
let's let move the last comma and then
what I'm going to do is we want the
output to be like this we want the
precision we want the actual date value
and the storage size okay so to get that
so I'm going to hard-code
the precision so whatever you see here
precision zero precision one I'm going
to hard-code that so I'm going to say
precision zero let's give it a column
name let's call it
precision since it's a keyword I'm going
to wrap it within square brackets all
right so the first column is precision
and then we want the actual date value
from the respective column and the first
column within that table variable is
this one date time to precision zero
okay so I want that column value they
time to precision zero and I'm going to
give again that column an alley as let's
call it day time and finally we want the
storage size and to get the storage size
I'm going to use data length function
and we want the storage size of this
column date time to precision zero and
let's give this column also an alley as
I'm going to call this storage size okay
and let's specify our table variable so
what is our table variable temp table
okay now let's execute this and see what
we get
notice what we get we get precision zero
and with precision zero notice that we
don't have milliseconds at all we get
the date part and then 15 hours nine
minutes 12 seconds and there are no
milliseconds now what I'm going to do is
you know apply a Union all operator and
then going to get the value for
precision one precision to so on and so
forth now the important thing to
remember here is since we have selected
for the first column the precision is
zero because that we don't have
milliseconds at all okay now let's go
ahead and change this query so I'm going
to apply Union all operator and then
make a copy of this query so now I'm
going to change the precision to
precision one and this time we want the
date value from daytime to precision one
column and we want the data length of
daytime to precision one column so we
applied the Union all operator now let's
execute the
square eat together and see what we get
look at that with precision one this is
the date we get look at the date look at
the precision we get in now the first
value one from the milliseconds okay and
look at the storage size it is six pies
so for precision zero and one so far the
storage size a six now what I'm going to
do is similarly apply the Union all
operator for the rest of the positions
and in the interest of time I have
already typed that required code so I'm
going to copy that and paste it right
here so now if you look at the query
again this is straightforward similar to
what we have done so far so we apply
this Union all operator so precision
three we are retrieving the value from
daytime to precision three column that
is this column right here okay
so it goes on similarly we want to you
know we are missing precision one
so let's actually make a copy of this
one and change this to precision two so
we want a precision to column and
precision to column data length so this
data line function is going to written
as the storage size all right so let's
go ahead and execute this and see what
we get now look at what we get so for
precision zero one two notice the
storage size it is six for precision
three for the storage size is seven for
precision 5 to 7 we get storage size 8
and look at precision 7 look at that
value it's exactly the value that we
have provided for the column you know 1
2 3 4 5 6 7 ok it's not rounded it's the
exact value so with seven digits
precision you know the accuracy that we
get is up to hundred nanoseconds okay so
obviously date/time 2 is more accurate
than date/time datatype thank you for
listening and have a great day