order by DAY no matter the year or month

Question

hello i have a problem i want to display dates by ordering them by day to now what birthday comes first, for ex: i have

15/08/2013 00:00:00
15/08/2013 00:00:00
29/08/2012 00:00:00
23/08/2012 00:00:00
16/08/2012 00:00:00
10/08/2012 00:00:00

i want it to be

10/08/2012 00:00:00
15/08/2013 00:00:00
15/08/2013 00:00:00
16/08/2012 00:00:00
23/08/2012 00:00:00
29/08/2012 00:00:00

i wrote:

public DataTable BirtdayReport(DateTime d)
{
    try
    {

        string query = "SELECT C.Class_Name as [כיתה], P.Person_First_Name as [שם פרטי], P.Person_Last_Name as [שם משפחה], P.Date_Of_Birth AS [תאריך לידה] ";
        query += "FROM Person as P INNER JOIN Customer as C ON P.[Person _ID] = C.Person_ID WHERE MONTH(P.Date_Of_Birth)=Month(@d) ORDER BY (DAYOFMONTH(Person.Date_Of_Birth)) ;";

        OleDbCommand command = new OleDbCommand();
        command.CommandText = query;
        command.Parameters.AddWithValue("@d", d);
        DataTable dt = AdoHelper.ExecuteDataTable(command);
        return dt;

    }
    catch (Exception ex)
    {
        throw ex;
    }
}

but it doesnt sort it like i want. can you help me?

Answer 1

select 
    col1, col2, col3, col4
from
(
SELECT 
    C.Class_Name as col1, 
    P.Person_First_Name as col2, 
    P.Person_Last_Name as col3, 
    P.Date_Of_Birth AS col4,
    DAYOFMONTH(Person.Date_Of_Birth) as ordCol
FROM 
    Person as P INNER JOIN 
    Customer as C 
        ON P.[Person _ID] = C.Person_ID 
WHERE 
    MONTH(P.Date_Of_Birth)=Month(@d) 
) tbl
ORDER BY tbl.ordCol

Answer 2

This is based on Standard SQL, but it should be easy to modify it based on your SQL dialect:

ORDER BY 
   CASE WHEN EXTRACT(MONTH FROM Date_Of_Birth) > EXTRACT(MONTH FROM CURRENT_DATE) 
         OR (EXTRACT(MONTH FROM Date_Of_Birth) = EXTRACT(MONTH FROM CURRENT_DATE) AND 
             EXTRACT(  DAY FROM Date_Of_Birth)>= EXTRACT(DAY   FROM CURRENT_DATE))
        THEN 0 
        ELSE 1
   END,
   EXTRACT(MONTH FROM Date_Of_Birth), EXTRACT(DAY FROM Date_Of_Birth)