0

I'm trying to return the title of books that were published in August 2014. I'm not sure what's wrong.

SELECT title_name
FROM titles
WHERE pubdate = '2014-08%';

I know how to find titles of books between two different dates, so shouldn't the format be the same except the greater than or less than sign is replaced with equals?

SELECT title_name
FROM titles
WHERE pubdate > '2014-07-15'
AND pubdate < '2014-08-15';
John Conde
  • 217,595
  • 99
  • 455
  • 496
Jake
  • 313
  • 1
  • 7
  • 16
  • http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates will give you more info about querying against dates – Maxqueue Mar 13 '15 at 00:13

2 Answers2

1

If you're going to use a wild card you need to use LIKE not =:

SELECT title_name
FROM titles
WHERE pubdate LIKE '2014-08%';

But a better way might be to just specify the month and year only (using MONTH() and YEAR()):

SELECT title_name
FROM titles
WHERE MONTH(pubdate) = 8 AND YEAR(pubdate) = 2014;
John Conde
  • 217,595
  • 99
  • 455
  • 496
0

Actually, you had the right idea in the first place. You missed it by thinking of "August 2014" as one date. It is not a date at all, it is an interval. An interval can be delimited by two dates, the date it starts and the date it ends. Or, in the case of an interval of a month, which is not a constant duration -- some are 30 days, some are 31, one is even less -- then use the start of the next month as the end date.

So the correct query will look amazingly similar to your example:

SELECT  title_name
FROM    titles
WHERE   pubdate >= '2014-07-01'
    AND pubdate < '2014-08-01';

And it has the added benefit of being sargable. That is, if pubdate is indexed, the index will be used. The search will start at the first book printed in August and stop when it reaches the first book printed after August. If you pass pubdate to a function, the index cannot be used and every pubdate in the entire table must be scanned.

TommCatt
  • 5,498
  • 1
  • 13
  • 20