How do I format a date in SQL?

Question

I have to meet the condition that the purchase date is before 2012 and I can't figure out how to format my SQL code to get this right.

My current code:

SELECT g.GameName, p.GameID, g.GameID 
FROM games g, purchases p 
WHERE p.GameID = g.GameID
AND p.DatePurchase < 2012-01-01

And the database is formatted in the same was (as in '2012-06-15') but when I run this code it runs but I get 0 results.

Answer 1

The problem was that the date value isn't quoted, so it's interpreted as an arithmetic expression 2012 - 1 - 1, i.e. 2010.

This is easily fixed:

... AND p.DatePurchase < '2012-01-01'

Answer 2

To compare year you can always use MySQL YEAR function.

SELECT g.GameName, p.GameID, g.GameID 
FROM games g, purchases p 
WHERE p.GameID = g.GameID
AND YEAR(p.DatePurchase) < '2012'

Answer 3

SELECT g.GameName, p.GameID, g.GameID 
FROM games g, purchases p 
WHERE p.GameID = g.GameID
AND p.DatePurchase < '2012-01-01'

If the date stored in the database is in datetime format, then just cast it as Date. Please look at the below code

   SELECT g.GameName, p.GameID, g.GameID 
   FROM games g, purchases p 
   WHERE p.GameID = g.GameID
   AND CAST(p.DatePurchase AS DATE) < '2012-01-01'