Selecting records based on month/year

[ajuly]

This is probably a very simple question, so I am hoping someone has an easy answer.

I have a table of records with a date field in M/D/Y format.
How do I select all the records for the month (and selected year)?

Thanks in advance

 

[TonyGroves]

I don't know what you mean by your date field being "in M/D/Y format"; standard MySQL DATE fields are stored in the format 'YYYY-MM-DD', so I'll presume that's how your field is stored.

To get all records for a selected month and year, you could use (among other methods):
SELECT * FROM table WHERE YEAR(datefield)=2004 and MONTH(datefield)=3

 

[TonyGroves]

... or, for possibly faster processing:
SELECT * FROM table WHERE datefield LIKE '2004-03-%'

 

[r937]

or for even faster processing, utilizing an index if there is one,

... where datefield between '2004-03-01' and '2004-03-31'

rewriting the above to run off CURRENT_DATE is a bit trickier, holler if you want the code for that


tony, i am not certain about LIKE '2004-03-%' requiring a table scan, but i suspect it does, because you are comparing character strings, so mysql would first of all have to convert the datetime value to a character string

dates in mysql are not stored as 'yyyy-mm-dd'

dates are stored as 3-byte numbers, datetime values as 8-byte numbers (not sure how, exactly)

see 11.6 Column Type Storage Requirements


rudy

http://r937.com/

SQL Consulting

 

[TonyGroves]

Rudy -

You're right about LIKE '2004-03-%' not using an index; I have tested it. I stand corrected. (Though, it's surprising the optimiser is not clever enough to convert that expression to a date range comparison). If it was a string field being compared, then an index would presumably be used.

When I said dates are stored as 'YYYY-MM-DD', what I meant was that as far as the normal user is concerned, that is how they might as well be stored, because that is how they are always presented to the user (of course, you can also use 20040301 and '20040301'), regardless of the internal storage format. The point I was making was that a MySQL date field is never accessed in the form M/D/Y. Judging by the nature of the question, I thought a simple (though possibly simplistic) answer would be appreciated.

In any event, maybe we're both barking up the wrong tree; maybe "ajuly"'s field is not a standard DATE field, as he/she implies.

 

[ajuly]

Thanks for the reply guys.
Actually my task is a bit trickier:

I need to select all the records prior to the selected month (and year), not including the month.
SELECT * FROM table WHERE YEAR(datefield)<2004 and MONTH(datefield)<3 would work for February 2004 but not December 2003 (as any other approach you mentioned)

I said it was simple but maybe not as simple as I thought.
Any ideas?
Thanks in advance.

 

[r937]

> "all the records prior to the selected month
> (and year), not including the month"

if the year is yyyy and the month is mm,

... where datefield < concat(yyyy,mm,'01')[/cpde]

rudy
[URL unfurl="true"]http://r937.com/[/URL] SQL Consulting

 

[ajuly]

Thanks rudy, this is the solution. I don't know whether it has something to do with the server set up but this is what does the job:

... where datefield < concat(yyyy,'-',mm,'-','01')[/cpde]