ansaurus

Question

How can I search for any day in a month?

Answer 1

+6 A:

No, you don't need to know the lengths of the months. Just do this:

WHERE MONTH(yourcolumnname) = $month AND YEAR(yourcolumnname) = $year

Mark Byers 2010-02-15 21:29:52

And the `$year` part?

Joren 2010-02-15 21:31:06

@Joren. Thanks, fixed.

Mark Byers 2010-02-15 21:31:40

what type of field would it have to be for this to work?

WillyG 2010-02-15 21:40:48

DATE, DATETIME, or TIMESTAMP - one of the column types that MySQL knows as a date, in other words. I don't believe you could do this on a VARCHAR column or anything.

ceejayoz 2010-02-15 21:41:53

@ceejayoz: In MySQL it also works on VARCHAR. It just returns NULL if the string isn't a valid date.

Mark Byers 2010-02-15 21:45:29

@Mark Byers Thanks, good to know. Imagine it'd be *faster* on one of the date types, though, as it doesn't have to guess/interpret.

ceejayoz 2010-02-15 21:46:13

so if my column was a timestamp column, would I replace datetime with timestamp?

WillyG 2010-02-15 21:55:57

iMaster: 'datetime' was a placeholder for the *name* of the column, not the type.

Mark Byers 2010-02-15 22:00:39

@iMaster: There is a reason I asked you in a comment to your question to provide more information about the name and type of your column. If you just tell us that information instead of leaving us to guess it, it will make it a lot easier to help you in an efficient way.

Mark Byers 2010-02-15 22:06:44

Answer 2

A:

Can you search between

between '1 '.$month.' '.$year and '1 '.$month_plus1.' '.$year

?

Have you tried running your sql statement against the data you are trying to match, that might be a good approach to begin with.

Toby Allen 2010-02-15 21:30:09

That'd include the first day of the next month, which I imagine would be suboptimal.

ceejayoz 2010-02-15 21:44:17

if you include time, it would be feasible to use this method also !... I use to add a month and substract a second or a millisecond

Jhonny D. Cano -Leftware- 2010-02-15 22:03:25

Answer 3

+1 A:

Your code looks o.k. in the sense that it will work, but a more elegant approach would be

SELECT * FROM tablename WHERE MONTH(columname) = '1'
                              AND YEAR(columnname) = '2009'

Pekka 2010-02-15 21:30:58

Answer 4

+1 A:

If it's a DATETIME or DATE field, you could also do SELECT * FROM table WHERE date_column LIKE '2010-02-%'; With indexing, this may be faster than the MONTH() and YEAR() technique, which'll have to run a calculation on each row.

ceejayoz 2010-02-15 21:37:57

ansaurus

tags:

views:

answers:

How can I search for any day in a month?

related questions