Date manipulation

1. Date manipulation

Now you're ready for more advanced date manipulation. In this lesson, we will introduce GETDATE() and DATEADD(), as well as expand on DATEDIFF() from earlier.

2. GETDATE

GETDATE() is a great SQL function that returns the current timestamp of the computer's operating system that is running SQL Server. You can use it instead of manually setting date values, which saves you time when executing queries. Your code will also be more consistent and less error-prone. You will use GETDATE() often when writing your own functions and stored procedures. Here you can see how to select GETDATE() and set a variable to its return value.

3. DATEADD

DATEADD() is another powerful date function. It adds an integer value to the date passed for the datepart specified. Remember datepart arguments from the beginning of the course? That's how you tell SQL Server which part of the date you'd like to manipulate. Here we are adding one day to the date value of 2/27/2019.

4. DATEADD and GETDATE

We can even combine DATEADD() and GETDATE() to select yesterday's date. There is no DATESUBTRACT function in SQL because we don't need it. We can just add negative numbers to get the same effect. What if we need to know how many passengers were picked up in taxis yesterday? We can use DATEADD() and GETDATE() in the WHERE clause of our SELECT statement. This query will return yesterday's passenger count regardless of the actual date by subtracting one from the value of GETDATE().

5. Remember DATEDIFF?

Remember DATEDIFF() from earlier? It's a SQL function that counts how many times the datepart boundary specified has been crossed between two dates. That's not always the same as the difference between two dates as we often think about it. The first example shows the day boundary has been crossed once between 2/27/2019 and 2/28/2019, as we would expect. But have there really been two years between 12/31/2017 and 1/1/2019? No, but the year boundary has been crossed twice. The return value of DATEDIFF() depends on the datepart argument you pass.

6. Let's nest

Let's find the first day of the current week by using DATEDIFF(), DATEADD(), and GETDATE(). Here we are nesting three SQL functions together. Don't worry, let's break them down individually. When evaluating nested functions in SQL it can be helpful to start from the innermost function. First, we are calling GETDATE() to return the current date. Second, we are calling DATEDIFF() to see how many weeks between today and 0, which is equal to 1/1/1900 in SQL Server. It's been 6,217 weeks since 1/1/1900.

7. Let's nest

By adding zero weeks to the 6,217th week with the DATEADD() function, SQL Server will return the date of the beginning of that week.

8. Now it's time for you to manipulate some dates!

Reading and writing nested date functions takes practice, so let's get started.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.