SQL Server Tutorial: 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 datedif from earlier. 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 store procedures. Here you can see how to select GetDate and set a variable to its return value. DateAdd is another powerful date function. It adds an integer value to the date passed for the date part 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 2272019. We can even combine DateAdd and GetDate to select yesterday's date. There's no date subtract 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 1 from the value of getdate. Remember datediff from earlier? It's a SQL function that counts how many times the datepart boundary specified has has been crossed between two dates. That's not always the same as the difference between two dates as we would often think about it. The first example shows the day boundary has been crossed once between 2272019 and 2282019, as we would expect. But have there really been two years between 12312017 and 11 2019? No, but the year boundary has been crossed twice. The return value of datedif depends on the datepart argument you pass. Let's find the first day of the current week by using datedif, 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 datedif to see how many weeks between today and zero, which is equal to 1 1,900 in SQL Server. It's been 6,217 weeks since. By adding 0 weeks to the 6,217th week with the dateadd function, SQL Server will return the date of the beginning of that week. Reading and writing nested date functions takes practice, so let's get started.