1. Performing arithmetic operations on dates
You can see that there are many functions you can use to manipulate dates and time.
You can extract date information from the operating system, select only parts of a date, both as numeric values and as their description, and you can reconstruct a date from parts.
In this video, you will learn how to perform arithmetic operations on dates, like adding an interval to a date or subtracting a date from another one and interpreting the difference.
2. Types of operations with dates
Performing arithmetic operations directly on two dates or between a date and a number is possible in SQL Server. It's better, however, to use date functions for doing this, and you will see why in a few moments.
With DATEADD(), you add a date part to a date and the result will be a new date.
DATEDIFF() allows you to find the difference in time units between two dates.
Let's explore each option.
3. Arithmetic operations
If you want to add or subtract two dates, you can use arithmetic operators. You should be careful when doing so because the results are not always what you would expect.
For example, let's create two datetime variables. If you are not familiar with this syntax, the DECLARE command creates variables that can be used in SQL queries. After the word DECLARE you give the variable a name, prefixed with an "at" sign, a data type and also its initial value. Initializing is optional.
In this example, date1 is added to date2 and then subtracted again.
The result represents the number of days that are added or removed from the initial date.
You can also perform operations between two dates and this is when the results may seem unexpected.
Take a look at the results of adding and subtracting the two dates. In SQL Server, the date is first converted to an integer and then it's being added to the initial date as an increase in number of days.
This is why you should use these operations carefully and think about using the following functions to get more manageable results.
4. DATEADD(datepart, number, date)
With DATEADD(), you can perform arithmetic operations on a date. The parameters are: the date part that will be affected, the number and the date we want to change. The end result is another date.
Here are some examples how we can use this function.
First, five years are added to the birthdate. Then, five years are subtracted. Next, something similar is done by adding and then subtracting 30 days.
You can see that the results of the calculations are correct.
If the birthdate column would have been a datetime, you could have also performed operations with time units, hour, minute, and so on.
5. DATEDIFF(datepart, startdate, enddate)
The DATEDIFF() function returns the number of units between two dates. The result is a number. The units can be everything from a year to a nanosecond.
This is how we can use it in a query. The first parameter is the date part we want to see, followed by the start date and then the end date.
In this case, the function was used to calculate the age of each participant at the time they first voted.
The results are presented in different forms: the age in years and also in quarters, days, and even hours. If you want to calculate the age in seconds, you can easily do it.
So, this is a powerful and easy-to-use function.
6. Let's practice!
The best way to get familiar with new functions is to put them into practice. Let's see what you have learned.