Filter Records with a NULL value
Try reloading this page, or reviewing your browser settings
This video segment explains filtering on date and time. Specifically, it shows how to write date- and time-based expressions in a WHERE clause.
About this video
- John Deardurff
- First online
- 23 November 2018
- Online ISBN
- Copyright information
- © John Deardurff 2019
In this video segment, we are going to discuss how to filter records by using a date field. So let’s get straight into our SQL server.
So as you can see, I’ve opened up a query editor window within my SQL server Management Studio, and I’ve written three SELECT statements. Before we actually talk about how to filter records by dates, we need to talk about how dates work within SQL server. So our first line I’ve written a SELECT statement that has four divided by one divided by 2012. We see that as a date, but SQL server sees that as math, where it’s trying to divide those numbers. On the second line, again, it looks like a date to us, but for SQL server that looks like it’s trying to subtract four minus one minus 2012. In the third statement, I’ve actually written a date so that how we see it but also how SQL server can see that as a date.
Let’s try to run these queries one at a time. So if I highlight the first SELECT statement, I just want that statement to run, not the other two. I’ll execute, and you can see it comes back a zero because it tried to divide four by one and then divide that by 2012, which gives us a very, very small number, point 0000 something. If I try to run the second line, this will actually see, make it more clear that it’s doing math, where I tried to, SQL server tried to subtract four minus one minus 2012. So four minus one is three, minus 2012 is negative 2009. But when I try to run a date where I have the single quotes around the date field, you can see that it actually comes back with a date.
And I can write my date any way I want. The actual standard that most people use, the international standard, is year, month, day, hour, minute, second, basically from the largest date value to the smallest. But we can write our dates any way we want.
Now you can see here I have two tabs. I have the tab that has our three SELECT statements on it. I’ve actually created another tab that has a SELECT statement already written for us. I created a second tab just by clicking on the New Query window, and that opened up a new tab for me. So here, in this query editor window, I’ve gone ahead and written a SELECT statement to select the SalesOrderID and the OrderDate field from the Sales.SalesOrderHeader table, which has a listing of all of our sales. I believe we have 31,465 rows or records. So that’s how many orders we’ve placed in this database.
Now I want to look for a specific order, so I’m going to type in a WHERE statement, where OrderDate, OrderDate equals, and let’s look for April 1st, 2012, because I know there’s an order there. I believe there’s actually five orders, so when I go and execute this query where OrderDate equals April 1st, I’ll execute and you see that there were, in fact, five rows, five records, five orders that were made on that date.
Now if I wanted to find a range of dates, I could put in where OrderDate is greater than, let’s go ahead and put greater than or equal to April 1st, 2012, and OrderDate is less than or equal to, let’s put August, August 31st, 2012, 2012. So when I execute this query, it’s going to show 1,622 records. So that’s how many orders that were placed between April 1st, 2012 and August 31st, 2012.
Now another way I could have actually typed this, I’m going to comment out this line, in comment out code, put dash-dash, and I’m going to put a different line. Now when you have comments in your code, that means that line of code’s not running. So I’m going to put a different WHERE clause that’s going to be very similar. I’m going to put OrderDate, OrderDate, but this time I’m going to type in BETWEEN, BETWEEN April 1st and August 31st, 2012. There you go. Single quote. And I’m going to type that in and it’s going to be very similar. You can see when I ran the code previously it was 1,622 rows. If I execute this code, I again get 1,622 rows. So I can write the code either way. And both statements, they were inclusive of April 1st, 2012, and August 31st, 2012. So the BETWEEN and AND statement is another way I can retrieve ranges. Not just for dates, but I can do this for numeric values as well.
So in this video, we have looked at how to filter records by using a date field.