SQL order by and SQL date

Question

I have 2 doubts related to SQL query's

id name update
1  some  2013-05-03
2  som   2013-05-08
3  smee  2013-06-05

1. How can i list items on a particular month (I want all records,year and date will not be specified I just want to check the month)

2. How can I arrange name in alphabetic order and arrange it as groups of names such as (limiting number of records =10)

Array A = names starting with A Array B = names starting with B

Answer 1

You can simply use datatype of the field as DATE or you can store any date as unix timestamp and then convert it whenever you want to show it.

Example: 1363979714 (ISO 8601:2013-03-22T19:15:14Z)

If you want list items on a particular date, you can write your query like this:

Month:

 Select * from tableName where update like '%-5-%'

day:

 Select * from tableName where update like '%-16'

year:

 Select * from tableName where update like '2013-%'

Answer 2

The easiest way, to fetch MONTH from a DATE or DATETIME type of fields is to use the MySQL's date-time function MONTH().

For your query, it shall be:

SELECT *
FROM tblName
WHERE MONTH( `update` ) = <month Number such as 5>

---

The second would need a more complex query. I'd rather use php to do the grouping better(as I've more control over that language).