how do i do a select for the month with a yyyy/mm/dd format ?

steamPunk

in my orders table i have a field containing a date in the yyyy/mm/dd format
I would like to be able to select all rows by month number - is it possible with the date in that format ?
or should i include a new field in my table where i enter just the month number ?

thanks

rulian

depends on your field type
if your field is a date timestamp then you should be able to
you can do

$SQL="SELECT `fields`, `fields`, date_format(`datefield`, '%m') AS `month`
FROM `mytable` ORDER BY `month` DESC ":

read up more on http://dev.mysql.com/doc/refman/6.0/en/date-and-time-functions.html#function_date-format

steamPunk

thanks for your reply

rulian wrote:
depends on your field type
if your field is a date timestamp

the field type is 'date' with a default of 0000-00-00

would it work with that ?

I looked at the page you posted and there's a lot of interesting stuff there - i noticed there was a function called 'month()'
which might be worth looking at

rulian

try the sql I gave you, it should still work
obviously, just fill you your sql info, it worked when I tested mine here but my fieldtypes are timestamps not just dates

steamPunk

yes
but in the end i realised that i needed to specify the year as well as the month (it's for sales orders archives) so in the end i did this :

WHERE ((commandes.comm_jour > '2007-11-31')AND(commandes.comm_jour < '2008-01-01')) ORDER BY commandes.comm_jour

in fact it's like this

WHERE ((commandes.comm_jour > '".$from."-31')AND(commandes.comm_jour < '".$to."-01')) ORDER BY commandes.comm_jour

i couldn't think of a better way

thanks for your help anyway