Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you're new to SQL Server you can often find the date and time data types and functions a little bit confusing just because there appears to be so many of them. We have multiple different data types for dates. We have multiple functions, DATENAME, DATEPART, DATEDIFF, DATEADD, but really you'll probably find yourself using the same two or three over and over again and it starts off with a very common function in SQL Server. The ability to say GETDATE. Even though it doesn't take any arguments we do need to use the parentheses here, both the opening and closing ones. I execute that and we get the date and time right now.
This is local on the machine, working with the machine's time zone. If you're more interested in getting the universal time code, you can say GETUTCDATE and execute that, but for most people most of the time GETDATE is just fine. Now what's often the case of course is that we don't want to have the full date and time. We're interested in one particular piece of this, maybe it's the month, maybe it's the day. Well if it's that straightforward, we can't actually just jump into that and I could say well what I'm interested in is the DAY.
Now you can just say GETDAY. There is a function called DAY. As we can see, it's showing up in hot pink, but when I open it it's expecting a date time to be passed into in. Well we just saw how to get a daytime, which was GETDATE, so I'll wrap GETDATE in the call to DAY, hit Execute, and we have 14 and it is the 14th of November right now so that seems to make sense. Now you'll find that there are the equivalents for MONTH and YEAR, but beyond that you might want to get a little bit more granular. Well you can do.
If you want to get to something very specific such as the day of the week, we use a different function called DATEPART but we call it the same way. DATEPART and let me just remove the rest because again you want to use IntelliSense when you getting used to this. DATEPART takes two arguments: an interval, really meaning what part of the date you are interested in, which piece of it, and what date do you want that part of. It sounds little silly but say we wanted to know the weekday off today's date.
Well, I can actually put in the word WEEKDAY, comma, and then we're getting the date right now, which we'd just call trusty old GETDATE and execute that and we get one back here. Because the way that this server is configured right now Sunday, which is the day I'm recording this on, is considered the first day of the week. Now that can differ depending on what country you've installed SQL Server on. If you actually want to check you can look for the configuration variable called DATEFIRST, execute that, and what it's actually telling me is, it sounds a little counterintuitive, but it's actually telling me is that as far as this server is concerned the seventh day of the week is the first day of the week.
Let me explain what that means. It basically means Sunday is considered day 1 and that's what DATEPART is actually returning for me here. Well what if I didn't want this one? What if I actually wanted a text value here? Yes I've got the DATEPART and it's the weekday, but I wanted the name of the week. How can I do that? Well there is another useful function that's called to DATENAME and what I can do with that one is actually just pass it the same information. I'm interested in the weekday, its that interval, and I'm saying tell it for today.
Of course I am using GETDAY. You'll typically be using one of the date fields that's in your tables, but this will the trick here. I execute that and it tells me yup we are Sunday. Now what you're probably seen is as I've started typing say the word DATE, we get things like DATEADD, DATEDIFF, DATENAME, DATEPART and we've seen DATEPART and we've seen DATENAME, so let's do DATEADD. This gives us the ability to add days or weeks or months or whatever you want to an existing date.
So if I want to say what is the date 30 days from today, I'm going to call DATEADD and tell it I'm interested in adding on days. How many? 30. Onto what? Onto today's date. Close that. It takes those as parameters, it figures out the result, and it returns the result here that says 30 days from today is the 14th of December. Well what if we wanted to go backwards? Now depending on how your mind works, you might be expecting to find a date subtract function, but no there isn't a date subtract function.
But what you can certainly do is pass in a negative amount, -30 days, or in this case let's say I'll go back to seven months ago. We return that and that apparently was the 14th of April. No particular surprise there/ Of course it is the 14th today here. The great thing is what we could decide to do is start to combine all of this. If we know that that's the date time value that we're retrieving I could then start to wrap it and say I want to call DATENAME, I'm interested in the weekday, and the value that I'm going to pass is what's return from combining DATEADD with GETDATE, again being very careful here on making sure that your parentheses are lining up where you have the right opening and the right closing ones, all needing to match of course. Then execute this and we find that seven months ago on the 14th of April it was a Wednesday.
As I'll continue to suggest, Books Online is a great help here the date and time data types and functions will actually break it down, even splitting those up into functions that modified date and time values, functions that get the difference were it be using the DATEDIFF function, just passing in two dates instead of one. And then being able to use some of the things we've already seen like DATEPART and DATENAME to pick certain pieces out of those date time objects.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97524 Viewers
80 Video lessons · 141062 Viewers
59 Video lessons · 59413 Viewers
52 Video lessons · 72735 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.