1. Math with Dates
In this lesson, we will show you how to perform some common math operations with dates.
2. DATEPART
DATEPART is used in T-SQL DATE functions to specify the unit of measurement, such as days, months, years, hours, etc. The common abbreviations as shown on the slide here are treated as keywords and allow you to specify which part of a date or time to use in a date function.
3. Common date functions in T-SQL
In this lesson, we will show you how to use two key date functions in T-SQL, DATEADD and DATEDIFF.
You can use DATEADD to add or subtract the DATEPART, you know the days, months, years, we talked about previously to a datetime value. Whereas DATEDIFF can be used to calculate the difference between two datetime values.
4. DATEADD
DATEADD has three required arguments. The first is the unit of measurement, the DATEPART, which we discussed in an earlier slide. The second is the number, an integer to add to date or subtract for the date. And finally, the date itself. Let's see this in action.
5. Date math with DATEADD (I)
Suppose you want to know the date 30 days from June 21, 2020, you can use the code shown here. You use DATEADD with the first argument, the DATEPART as DD, since you want to add a certain number of days. The second argument is 30, as you want to add 30 days, and finally, the last argument is the date to which you want to add 30 days, June 21, 2020.
6. Date math with DATEADD (II)
DATEADD allows you to look at dates either in the past or future. If you want to look back, use a negative number as the second argument to DATEADD.
Now if you want to know the date 30 days before June 21, 2020, you can use similar code. This time instead of using positive 30 as the second argument, you can use negative 30 to indicate that you are looking for a date in the past.
7. DATEDIFF
Similar to DATEADD, DATEDIFF takes three required arguments. The first argument is the same, datepart, a unit of measurement. The second and third arguments are the start and end dates, respectively. Let's see DATEDIFF in action.
8. Date math with DATEDIFF
Here we are counting the number of days between May 22, 2020 and June 21, 2020 as Difference1, and July 21, 2020 and June 21, 2020 as Difference2 using DATEDIFF. Since we want to know the number of days, the first argument is DD. Notice that if the start date is less than the end date, the difference is positive, whereas if the startdate is greater than end date, the difference is negative value to denote the date is in the past.
9. Let's practice!
Now it's time for you to explore dates in T-SQL!