CODEWITHSUNDEEP
phpmysqlsqldate
Peter White
Peter White

Reputation: 1086

SQL: How to select where date field is specific month?

I'm trying to select all invoices entered in a specific month.

The 'entry_datetime' field in the database is in the format of '2013-02-19 14:47:42'.

I'm using MySQL.

This is what I have so far:

SELECT
    id
FROM
    invoices
WHERE
    entry_datetime > 2012/12/31 AND entry_datetime < 2013/02/01

Unfortunately it's not returning any results but I can see a qualified invoice via phpMyAdmin.

Please can you tell me what is wrong with the where clause?

Upvotes: 2

Views: 22380

Answers (3)

developerCK
developerCK

Reputation: 4506

Another way to do it:

SELECT
    id
FROM
   invoices
WHERE
    entry_datetime between '2012/12/31' AND '2013/02/01';

And if you want to get result of a specific month then use function like: month() as:

SELECT
    id
FROM
   invoices
WHERE
   MONTH(entry_datetime)=12;

where 12 is month mumber

Upvotes: 7

Lloyd Santos
Lloyd Santos

Reputation: 402

Since the dates in the where clause are not enclosed in quotes, MySQL assumes that it's a mathematical formula/equation. So it evaluates 2012/12/31 as something like 5.4086.

Upvotes: 0

Iswanto San
Iswanto San

Reputation: 18569

Try this : 

SELECT
    id
FROM
    invoices
WHERE
    entry_datetime > '2012/12/31' AND entry_datetime < '2013/02/01'

Upvotes: 2

Related Questions