DateTime2FromParts function in SQL Server 2012 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 128 of sequel server
tutorial in this video well discuss date
time to from parts function in sequel
server this a new function introduced in
sequel server 2012
it returns date time - value here is the
syntax for this function
date time - value has got two parts with
a net the date part and the time part
for this function to construct the date
part it requires three pieces of
information here month and they and for
the same function to construct the time
part it requires our minute seconds
fractions and precision
well discus fractions and precision
parameters ingest a bit but these are
the parameters required for this
function all these parameters have a
data type of integer if any of these
parameters have invalid values then the
function returns an error
similarly if any of these parameters are
null this function is going to return
now but one important thing to keep in
mind is that if the precision parameter
is specified as now then this function
is not going to return null instead it
will return an error we have an example
here notice we have specified valid
values for all the parameters here is
2015 month is 11 day is 15 20 hours 55
minutes 55 seconds fractions is zero and
precision is zero so when we execute
this we get November 15 2015 20 hours 55
minutes and 55 seconds I have the same
query here so when we execute this we
get the date that we expect
now let's discuss about the fractions
and precision parameters the fractions
parameter depends on the precision
parameter for example if I specify
fractions as 1 and if I specify
precision as 7 then each fraction here
represents 100 nanoseconds so in this
case we are going to get 100 nanoseconds
similarly if I specify a precision value
of 3 then each fraction here represents
a millisecond so if I have 5 as the
value for fractions parameter then that
means you know these two parameters are
going to give us 5 milliseconds
when we execute this look at the
milliseconds bit here we get five
milliseconds first of all notice the
precision is three similarly if I
specify a precision of 7 the first thing
to notice is we have got a precision of
seven there and we have 500 nanoseconds
there so the maximum value for precision
is 7 if I specify a value beyond seven
we are going to get an error notice what
this error says it says specified scale
8 is not valid the maximum value for
precision is 7 another way of thinking
this precision parameter is like this so
I have a value of 5 of fractions if I
specify precision as 1 you can think of
that as 5/10 okay if I specify precision
as 2 then 5 divided by 100 if I specify
the precision 3/5 divided by 1000 so
what is 5 divided by 1000 5 divided by
thousand is going to give us 5
milliseconds point zero zero five so
when we execute this we should get 5
millisecond similarly precision is 4
that means 5 divided by 10,000 it goes
on like that and in the value of 7 now
all the values have to be valid values
let's say for example 4 month parameter
if I specify the value as 15 though it
is integer it's not a valid value for
month so let's put these values back to
0 so when we execute this this function
is going to throw an error because
that's not a valid value okay so let's
put that valid value back and similarly
if we specify any of the parameters as
now then the function is going to return
null now on the other hand if we specify
a null for precision the function is not
going to return null instead we get an
error we have all those examples right
here another new function introduced in
sequence over 2012 is time from parts
function from the name you can easily
tell this function is going to return
just the time
bit and to construct the time bit this
function requires you know hour minute
seconds fractions and precision
parameters notice this function does not
have the date pass that is the year
month and day okay it has only the time
pieces that this function requires so
this is going to return a time value so
the usage of this function is very
similar to the usage of daytime to from
past function it's just that we don't
have the parameters for constructing the
date part all right in our previous
video sessions we discussed about
date/time from parts function what is
date/time from parts function going to
do it's going to return a date/time
value and we have another function here
date/time - from parts function this
function returns date/time to value so
what is the difference between date time
and date time - we'll discuss that in
detail in our next video session thank
you for listening and have a great day