Possible to create a query that sorts by day of week?

Is it possible to write a SQL query that sorts data set by day of week starting from a specific day?

For example, if today is Thursday, the results are sorted from THU-FRI-SAT-...-MON-TUE-WED.

If today is Tues开发者_如何学JAVAday, the results would be sorted from TUE-WED-THU-...-SAT-SUN-MON.

Days are stored as integers.

Assuming you have the day of the week stored into field WD where value 1 means MON, 2 means TUE etc and SW is the "start of the week" index (again 1:MON, 2:TUE,...) then something like

CASE WHEN WD < SW THEN WD + 7 ELSE WD END

should give you a value to order by. I don't use sqlite so I'm not sure can you put it right into the ORDER BY or do you have to use it as a field and then order by that field.

in mysql: ORDER BY DATE_FORMAT(date,%w)

see: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Microsoft databases suport this (not shure) ... ORDER by DATENAME ( dw , table.datefield )

Check out DATEPART:

http://www.tizag.com/sqlTutorial/sqldatepart.php