SQL Server Tutorial: Date manipulation

Key Takeaways

now you're ready for more advanced date manipulation in this lesson we will introduce get date and date add as well as expand on date diff from earlier get date is a great sequel function that returns the current timestamp of the computer's operating system that is running sequel 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 get date often when writing your own functions and store procedures here you can see how to select get date and set a variable to its return value date add is another powerful date function it adds an integer value to the date passed for the date part specified remember date part arguments from the beginning of the course that's how you tell sequel server which part of the date you'd like to manipulate here we are adding one day to the date value of 227 2019 we can even combine date add and get date to select yesterday's date there's no date subtract function and sequel 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 date add and get to eight 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 get tape remember data from earlier it's a sequel function that counts how many times the date part boundary specified 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 227 2019 and 228 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 date diff depends on the date part argument you pass let's find the first day of the current week by using day diff date add and get date here we are nesting 3 sequel functions together don't worry let's break them down individually when evaluating nested functions in sequel it can be helpful to start from the innermost function first we are calling get date to return the current date second we are calling date death to see how many weeks between today and zero which is equal to one one 1900 in sequel server it's been six thousand two hundred and seventeen weeks since one one 1900 by adding zero weeks to the six thousand two hundred and seventeenth week with the date add function sequel server will return the date of the beginning of that week reading and writing nested date functions takes practice so let's get started