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

this is part 127 of sequel server
tutorial in this video we'll discuss the
difference between daytime and small
date time data types in sequel server we
have the differences here in the table
let's understand these differences with
examples will use this employees table
in this demo notice this table has got
two columns small date time and date
time and the data types of these columns
are small date time and date time
respectively the first difference is
from the date range perspective if look
at the date range for small date time it
is January 1st 1900 to June 6 2079 and
for daytime it is January 1st 1753 to
December 31st 9999 obviously the date
range for daytime datatype is much
bigger than small date time datatype if
you look at the query right here notice
we are inserting a date value for this
model date/time column and if you look
at this model date/time datatype it's
date ranges from January 1st 1900 to
June 6th 2079 and if you look at this
date value right here this is less than
the minimum value so when we execute
this query obviously we are going to get
an error and if you look at what the
error says it says out of range value
and if you look at this value right here
this is greater than the maximum date so
again when we execute this query we are
going to get the same error and if you
look at this query right here we are
inserting a date value for date/time
column in the employees table and if you
look at the date/time datatype its date
range is from january 1st 1753 to
December 31st 9999 and if you look at
this date value right here this is less
than the minimum date so again when we
execute this query we are going to get
the same error so obviously depending on
the data type that you pick the values
that you supply must fall within the
date range otherwise you are going to
get out of range error the next
difference is from the time range
perspective for small date/time it is
from 0 / 0 minutes 0 seconds to 23 hours
59 minutes and 59 seconds for daytime it
is 0 / 0 - 0
seconds to 23 hours 59 minutes 59
seconds and 997 milliseconds and if you
look at the accuracy for small date time
it is up to a minute and for daytime
datatype it is three point three three
milliseconds so obviously daytime data
live has got more accuracy than small
daytime datatype let's understand what
you mean by this accuracy so if you look
at either the date time datatype or
small date time datatype you know they
have got two parts in them one is the
date part and the other one is the time
part and if you look at the time part it
has got hours minutes seconds and
milliseconds look at the value right
here so this is the date part and here
is the time part and if you look at the
time it is 12 hours 35 minutes 29
seconds and 998 milliseconds so for
small date/time datatype you know if you
have a seconds value of twenty nine
point nine nine eight
so here twenty nine is seconds and the
value that is present after the dot are
milliseconds okay so if the value is
twenty nine point nine nine eight
seconds are lower then that time will be
rounded down to the nearest minute so if
you look at the time right here it is
twelve hours 35 minutes 29 seconds 998
milliseconds now when I execute this
query since I am inserting this value
for small date/time column whose data
type is small date/time the time value
will be rounded to the nearest minute
okay so since this is twenty nine point
nine nine eight okay any value that is
twenty nine point nine nine eight
seconds or lower will be rounded down so
in this case the time will be stored as
twelve thirty five twelve hours 35
minutes and zero zero seconds let's
actually look at that in action execute
the query it inserts the date and time
and when we select the data back from it
look at that twelve hours 35 minutes and
0 seconds it is rounded down to its
nearest minute let's truncate the data
now let's look at the next query so if
you look at this query right here now we
have the same time - 11:35 mins but this
time the seconds value is twenty nine
point nine nine nine okay so if you have
values with twenty nine point nine nine
nine seconds or higher then that time
will be rounded up to the nearest minute
so in this case the time will be rounded
to twelve thirty six okay so let's
execute this and see what we get so when
we select the data notice now the time
is twelve thirty six that's why the
accuracy for small date data type is up
to a minute
whereas for daytime data type it is
three point three three milliseconds
let's understand this again with an
example so let's truncate the data that
we already have an employee's table now
one important thing to keep in mind is
for small date/time datatype if the
value for seconds is two point nine nine
I'm in twenty nine point nine nine eight
or lower then it will be rounded down to
the nearest minute if it is twenty nine
point nine nine nine or higher then it
will be rounded up to the nearest minute
whereas if the data type is date/time
datatype and if you have these
milliseconds you know actually the
milliseconds will be rounded in
increments of these three values here
okay point zero zero or point zero zero
three or point zero zero seven okay
those are milliseconds all right and if
you look at the time right here look at
the milliseconds specifically okay nine
nine zero nine nine one so in these two
cases the time will be rounded to 0.99
zero okay because zero is the nearest
one for zero and one okay so let's
execute these two queries and select the
data so now we expect the data I mean
the time basically to be 23 hours 59
minutes 59 seconds and 990 milliseconds
so when I select the data notice that we
get 990 as the milliseconds
let's truncate this and if you have
hey you know 9 9 - or 9 9 3 or 9 9 4 in
this case it will be rounded - 9 9 3
because remember what we have said it
will be rounded - increments of zero
zero zero zero zero 3 or zero zero seven
and for all these three values zero zero
three is the nearest one so when we
execute these three queries we expect
the time to be 23 hours 59 minutes 59
seconds and milliseconds to be 993 so
when we select the data look at that for
all the three values we get 993 and in
this case the nearest value is 997 so
nine nine five nine nine six nine nine
seven nine nine eight all of them will
be rounded it to its nearest increment
which is nine nine seven
so let's truncate the data from
employees table and execute these
queries and let's select the data and
notice everything is rounded to nine
nine seven and finally we have nine nine
nine so it will be incremented - I mean
rounded to nearest zero so in this case
since the time is twenty-three hours 59
minutes 59 seconds and 999 milliseconds
the date is going to change in this case
so let's truncate the data and let's
insert this so the date now will have to
be you know second January 1995 and
hours minutes seconds and milliseconds
everything will become zero notice that
okay January 2nd 1995 so that's the
difference as far as the accuracy is
concerned obviously date/time datatype
is more accurate than small date/time
data type and the size obviously small
date/time takes four bytes
whereas day time takes eight bytes twice
the size of small date/time datatype and
the default value for both the data
types is the same january first nineteen
hundred year over 0 minutes and 0
seconds thank you for listening and have
a great day