SQL Where Archives - Page 2 of 3 - SQL Training Online

October 25, 2012 by Joey Blue Leave a Comment

SQL Date Comparison – How to Filter Datetime in SQL Server

Video: SQL Date Comparison – How to filter Datetime in SQL Server – SQL Training Online
In this video, I show you how to do a SQL Date Comparison in SQL Server when your Datetime has an actual time in it. I also include the latest trick that will make this easier if you are using SQL Server 2008 or SQL Server 2012.

[sharebox5_no_text] [/sharebox5_no_text]

How do you compare a Datetime in SQL Server?

A SQL Datetime stores both Date and Time. So when you are filtering using the SQL Where clause, you have to know if you Datetime is storing the time also.

Let’s take a look at the employee table in SQL Training Online Simple DB

select *
from employee

The column we are looking at is the hire date. I want to go after the person who was hired on February, 22 2005.

So, I use the following SQL.

select *
from employee
where hire_date='02/22/2005'

Now, this works because we don’t have any times stored in this column. But, if I change the record from 2005-02-22 00:00:00.000 to 2005-02-22 08:22:25.000 and rerun the query, you see that I don’t get any results.

SQL Date Comparison

That is because the value of 02/22/2005 is really 2005-02-22 00:00:00.000 and I just changed the hire_date to include a time other than 00:00:00.000.

So, I have to tweak my query to use a greater than and less than sign.

select *
from employee
where hire_date >= '02/22/2005'
and hire_date < '02/23/2005'

This works. But, to really understand what is going on, let me show you what SQL Server really sees.

select *
from employee
where hire_date >= '2005-02-22 00:00:00.000'
and hire_date < '2005-02-23 00:00:00.000'

There are other ways, and in this StackOverflow article you can read more about these other ways. But, I want to point out a few.

If you are working in SQL Server 2005 or earlier, the following is supposed to be the fastest performing way to do this.

select datediff(dd,0, hire_date),*
from employee
where dateadd(dd,0, datediff(dd,0, hire_date)) = '2005-02-22'

But, if you are working in SQL Server 2008 or SQL Server 2012, they have introduced an actual Date datatype that doesn’t include the Time portion. Since this is the case, you can use the CAST function to remove the time from the Datetime.

Here is the SQL to do that.

select *
from employee
where CAST(hire_date AS Date) = '2005-02-22'

And that’s it. That is the SQL Date Comparison in Microsoft SQL Server.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

June 14, 2012 by Joey Blue Leave a Comment

SQL Order By

The SQL Order By allows you to sort your results when you run a SQL Query.

Let’s look at an example:

select * from employee order by employee_name;

This will sort by the employee_name column.

Here are the results:

If you want to change the sort from Ascending (which is the default) to Descending, you would write your query as follows.

select * from employee order by employee_name desc;

With the following results:

You can also sort by more than one column.

select * from employee order by job asc,employee_name desc;

Notice how job and employee_name columns are both sorted.

And the last trick is that you can use the column number. In this case I will sort by the 3rd column (job) then the 2nd column (employee_name).

select * from employee order by 3 asc,2 asc;

Notice that I changed the employee_name column to sort ascending.

And that’s it for the SQL Order By clause.

Leave any comments or questions below.

June 13, 2012 by Joey Blue Leave a Comment

SQL Like

The SQL Like is the clause that allows you to do wildcard searches in the SQL language.

The Like clause can only be used on string data types (char and varchar in Microsoft SQL Server).

You can use it in a SQL statement anywhere you would normally use the “=” (equal sign).

There are two wildcard characters you can use:

% – Is a multi-character wildcard. Which means it will match more than one character.
_ – The underscore is a single character wildcard. It only matches one character.

Let’s take a look at a few examples.

I want to look at a student table:

select * from student;

Here are the results:

So the student table has 7 rows and 8 columns.

Now, lets find all of the addresses that are designated “Rd”.

I have to do this by searching with the SQL Like clause.

Here is the query to do that:

select * from student where S_ADDRESS LIKE '%Rd%';

The where clause says, “I want to look at the S_ADDRESS column and find everything that has any number of characters, then an Rd, then any number of characters.

Here are the results:

We get 3 records that contain the “RD”.

No what if I wanted to find all of the zip codes that had a third character of 1?

The query would look like this:

select * from student where S_ZIPCODE LIKE '__1%';

Let’s take a look at that LIKE string: ‘__1%’

The first part of that string is actually 2 underscores. That is because we want exactly 2 characters, then we want to see the number 1. (I say number, but it is actually a varchar in the database.)

Here are the results:

I have also used it in a CASE statement inside of the SELECT clause, but I will leave that for another post.

As always, if you have any suggestions for topics you would like for me to blog, or if you have any general comments, leave them below.

June 12, 2012 by Joey Blue Leave a Comment

SQL Wildcard

SQL Wildcard filters allow you to search within a string to do partial matches.

So you can use them to search for all names that begin with the letter ‘A’.

Or you could search for all addresses that contain the name ‘OAK’.

Let’s take a look at an example. I will start with a basic employee table and run the following SQL query:

select * from employee;

The results look like this:

I want to find all of the employee’s with a name starting with ‘A’.

In order to do this, I need to use a SQL Where clause along with a LIKE wildcard filter.

The SQL will look like this:

select * from employee where employee_name like 'A%';

And here are the results:

I filtered out the 2 rows that have an employee_name starting with ‘A’. (ALLEN and ADAMS).

Let’s look at the where clause of that statement in more detail:

where employee_name like 'A%';

Where – Starts the filter section of SQL

employee_name – Is the column we want to look at.

like – The comparison operator. This is how we want to compare. Some other options would be =, <= ,>=, between, in, etc..

‘A%’ – This is the value we are looking for. We have to enclose it in ‘ ‘ (called ticks). Since the first letter inside of the ticks is an A, it will match the first character. Then we have the %. This means we don’t care how many characters come after the A. The name could contain just the A with no letters after it. It could contain the A with 1 letter after it, or it could contain the A with 100 letters after it. The % doesn’t care.

Now, there is one other wildcard that is often used. It is the ‘_’ (underscore). This is a single character wildcard.

Let’s look at an example. I want to find all names where the second letter is ‘L’:

select * from employee where employee_name like '_L%';

Here are the results of the query:

In each of the 3 results, the second letter of the employee_name is an L.

That is how you use the wildcard filter in the SQL language.

Please leave any questions and comments below.

June 11, 2012 by Joey Blue Leave a Comment

SQL Between

The SQL Between is a way of looking for a range of values when you are trying to filter in a SQL Select statement.

So let’s take at an Employee table with the following query:

select * from employee;

Here are the results:

This employee table has 14 rows. I want to filter this table to find all of the salaries that are greater than or equal to 1100.00 and less than or equal to 1600.00.

I will use the following query to do that:

select * from employee where salary >= 1100 and salary <= 1600;

Here are the results:

Notice how we get the salary of 1100 and the salary of 1600. This is because I used the greater than or equal to and the less than or equal to.

I could reword what I want with the following sentence. I want to filter this table to find all of the salaries that are between 1100.00 and 1600.00.

Then I can write the query like this:

select * from employee where salary between 1100 and 1600;

This query will give you the same results as before, but it uses the BETWEEN clause.

Some things to note about the BETWEEN clause.

First, you only put the column name in there 1 time vs. 2 times with the first query.

Also notice that the BETWEEN clause includes the Endpoints. So, if you look at the query above, we are including in the results a Salary of 1100 and a Salary of 1600.

Now, if you are wondering if one way is better than the other? Whether you should use the BETWEEN clause or the ‘>=’ and ‘<=’?

I would have to say that it is really just a preference. I prefer to use the BETWEEN clause when I can because it helps with the readability of the query.

As you get into more complex queries, you will start to find ways to keep your query as simple and readable as possible so that you don’t loose your mind.

(Trust me, queries can become really complex fast!)

That’s it for the BETWEEN clause.

Drop me a note in the comments section if you have any questions, comments, or suggestions.