-4

I'm new to SQL, learning the basics. I searched, but did not find the exact answer I needed that would work.

I have a table that shows customers who have purchased products and the datetime they were purchased. The column is called SaleDate. The format (for example) of the datetime looks like this:

2015-08-21 00:00:00.000
2014-03-17 00:00:00.000

I need to use a query to only show the products purchased during December of 2015. I don't think the SELECT or FROM statements are relevant to my question. Here are the two WHERE clauses I tried. Neither of these worked.

WHERE  SaleDate = '2015-12%'
WHERE  SaleDate = '%Dec-2015%'
Hyperlite147
  • 25
  • 1
  • 7
  • There is a useful answer below, esp. for DateTime columns, but be aware that if you are trying to match a **string** using the '%' sign, you probably want to use `WHERE Column LIKE ...`. – Charlie Joynt Feb 22 '16 at 20:59
  • 1
    Possible duplicate of [WHERE Clause to find all records in a specific month](http://stackoverflow.com/questions/851236/where-clause-to-find-all-records-in-a-specific-month) – Tab Alleman Feb 22 '16 at 21:01
  • While that was asked previously the approved answer there is NOT a good one. It wraps the table columns in a function which renders any indexing on the column useless and will always force a scan instead of a seek. – Sean Lange Feb 22 '16 at 21:04
  • The accepted answer here is a duplicate of a proposed answer in the duplicate question. I don't think we should write off a duplicate just because the accepted answer isn't the best. Out of all the proposed answers, I like the DATEDIFF() answer the best. I still say this is a dupe. – Tab Alleman Feb 22 '16 at 21:07

2 Answers2

2

You can use BETWEEN:

WHERE SaleDate BETWEEN '2015-12-01 00:00:00' AND '2016-01-01 00:00:00'
Adam V
  • 6,256
  • 3
  • 40
  • 52
  • `BETWEEN` is inclusive. This means that any sales that occurred on `2016-01-01 00:00:00` will be included in the result set. This is unlikely to be an issue if the time portion is actually set but many times `DATETIME` fields are just set to the date with the default `00:00:00`, `WHERE SaleDate >= 2015-12-01 00:00:00 AND SaleDate < 2016-01-01 00:00:00` is probably a better choice. – Kidiskidvogingogin Feb 22 '16 at 20:57
  • Thanks Adam V. That worked after I added the ' to the sides of the datetime. – Hyperlite147 Feb 22 '16 at 20:57
  • @Kidiskidvogingogin - if you think you might get an order placed at exactly midnight on New Year's Eve, you can also change the end to "2015-12-31 23:59:59.997", but that's less obvious and relies on the timestamp rounding features of SQL. – Adam V Feb 22 '16 at 20:58
  • Need to add '' around your date literals. – Sean Lange Feb 22 '16 at 21:02
  • Sean, that's correct. I am new to SQL but I did catch that part :) – Hyperlite147 Feb 22 '16 at 21:04
  • Do take the warning from @Kidiskidvogingogin. This will return values where the SaleDate = '20160101' which is not 100% accurate of all values in 2015. – Sean Lange Feb 22 '16 at 21:06
  • Yes, I agree. Thanks – Hyperlite147 Feb 22 '16 at 21:16
0

Use YEAR and MONTH functions

... WHERE YEAR(SaleDate) = 2015 AND MONTH(SaleDate) = 12

Don't use BETWEEN because you must to express the dates as literals, and that will be a problem considering that your SaleDate column could come with the time part in the future...

Balde
  • 590
  • 3
  • 8
  • Someone posted this same method earlier, but for some reason deleted the answer. This is the one I actually went with. You make a very good point. For my specific situation, that won't be relevant, but in a real world situation, you're correct. Thanks! – Hyperlite147 Feb 22 '16 at 22:33