Help with MySQL Query

[Jamwa]

I have this query:

mysql> select distinct date_format(MessageTimeStamp, '%M %e') as Date, count(date(MessageTimeStamp)) as Count from dbCDR group by date(MessageTimeStamp) limit 165, 5;
+----------+-------+
| Date     | Count |
+----------+-------+
| July 29  |   769 |
| July 30  |   271 |
| July 31  |   314 |
| August 1 |   314 |
| August 2 |    65 |
+----------+-------+
5 rows in set (0.04 sec)

Works fine, thanks. However, I want only the first three letters of the month displayed. That is, Jul and Aug instead of the whole name of month. Please help

[gadgetwiz]

Greetings,

SQL supports various string functions, including ..

LEFT(str,len)

Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

http://dev.mysql.com/doc/refman/5.0/...functions.html

You could also make use of the SUBSTRING() function.

[Jamwa]

Tried all that without success:

mysql> select distinct LEFT(date_format(MessageTimeStamp, '%M %e'), 3) as Date, count(date_format(MessageTimeStamp, '%M %e')) as Count from dbCDR group by date(MessageTimeStamp) limit 165, 5;
Empty set (0.12 sec)

mysql> select distinct substr(date_format(MessageTimeStamp, '%M %e'), 0, 3) as Date, count(date(MessageTimeStamp)) as Count from dbCDR group by date(MessageTimeStamp) limit 159, 7;
Empty set (0.04 sec)

mysql>

---------- Post added at 01:31 AM CDT ---------- Previous post was at 01:15 AM CDT ----------

Got it! date_format(MessageTimeStamp, '%b %e') gives me what I want

[assen]

Hi,

In short: use %b instead %M in your original query and forget about substr().

Or, better, before so, check the MySQL manual - at least, the date_format() options: http://dev.mysql.com/doc/refman/5.1/...on_date-format

WWell,