Monthly Reports

[pastorandy]

I would like to be able to pass a Month like 'Jan' which is a datetime field in a table called sales from a mysql db and pull back all sales for Jan 2007.

I can't get the following hard coded month to bring back the sales it's just empty.

SELECT Date_Format(order_date, 'mmm') AS monthDate, SUM(set_cost) AS set_cost2 from sales
WHERE order_date = 'Jan'
GROUP by monthDate";

Any ideas?

 

[vacunita]

According to the manual entry for Date_format to get the abbreviated form a a month. you use %b not MMM however may I suggest you use the numeric value of the month for comparisons instead of the string format.

In any case from your example:

SELECT Date_Format(order_date, '[red]%b[/red]') AS monthDate, SUM(set_cost) AS set_cost2 from sales
WHERE [red]monthDate[/red] [blue]LIKE[/blue] 'Jan'
GROUP by monthDate";

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.