1. Building dates from parts
We've seen some of what can be done with dates in SQL Server. We will now take a step back and look at how to create dates. We will build dates from component parts using a few T-SQL helper functions.
2. Dates from parts
SQL Server has six functions to build dates and times from component parts: the "from parts" series.
The first is DATEFROMPARTS(), which takes integer values for year, month, and day and returns a DATE type.
Its counterpart is TIMEFROMPARTS(), which takes integer values for hour, minute, second, fraction of a second, and precision--from zero to seven--and returns the appropriate TIME data type.
The classic DATETIME data type has its own function, DATETIMEFROMPARTS() which goes down to 3-millisecond granularity.
For more precision, we can build a DATETIME2 from parts using the DATETIME2FROMPARTS() function. Note that its inputs are the set combination of DATEFROMPARTS() and DATETIMEFROMPARTS().
There is a legacy function, SMALLDATETIMEFROMPARTS(). At this point, we recommend shying away from the SMALLDATETIME data type and just using DATETIME2 instead.
Finally, DATETIMEOFFSETFROMPARTS() takes the DATETIME2 fields and adds inputs for the hour and minute offsets from UTC time so we can specify the time zone as well as time.
3. Dates and times together
The DATETIMEFROMPARTS() and DATETIME2FROMPARTS() functions will return complete dates. Let's look at an example.
On November 11th, 1918 at 5:45 AM, the French and German governments signed an armistice, bringing major fighting during World War I to a close. We can represent this time using either a DATETIME or a DATETIME2 type. To show specifics, I made up the number of seconds, saying it ended at 17 seconds and 995 milliseconds.
The results are close to what we would expect with one subtle difference. Because the DATETIME data type is only precise to a three millisecond period, SQL Server rounds our result to the nearest allowed value, which is 997 milliseconds. The three DATETIME2 types allow us to choose our desired precision.
4. Working with offsets
We will cover offsets in more detail in later. For now, here is a quick example.
India Standard Time is 5 1/2 hours ahead of UTC. We can represent this in SQL Server using the DATETIMEOFFSETFROMPARTS() function, passing in 5 hours and 30 minutes as the offset values. We can also take this offset and bring it to another time zone using the AT TIME ZONE operator. Here, we bring it back to UTC.
We can see that at 9 PM India Standard Time, it is 3:30 PM UTC.
5. Gotchas when working with parts
There are three things to keep in mind when working with the FROMPARTS() series of functions.
First, if any of our input values is NULL, the result will always be NULL.
Second, if any of our input values is invalid for the date part, we will receive an error message stating that arguments have values which are not valid. This will also pop up for example if we have a `DATETIME` with a year before 1753.
Third, if we set the precision on DATETIME2FROMPARTS() to a number which is smaller than can hold the fraction part of your date, we will receive an error.
6. Let's practice!
Now that we've seen a few examples of building dates and times from parts, let's try doing it in a few exercises.