order a query by days of week starting with monday

[umair123]

hello. suppose a table XYZ has a column buydate. and there are 14 rows in it, 2 for each day of the week. if i would like to sort a query by days of the week, but starting from monday, how would i be able to do that? i have tried this query :
select * from XYZ order by to_char(buydate,'D') ;

this would bring sunday as the first row.
i dont want sunday as the first row, i want monday.

i am using oracle 10g database

[PAVB]

/ / / decode(to_char(buydate,'D') ,1,7,2,1,3,2,4,3,5,4,6,5,7,6,to_char(buydate,'D'))

[ebrian]

Another option:

order by decode(to_char(buydate,'D'), 1, 8, to_char(buydate,'D'))

[umair123]

could you explain why you chose this approach?

[PAVB]

could you explain why you chose this approach?

mmhh... may be because it solves the issue?

[umair123]

thanks...but would you please also explain why you chose that approach? thanks

[gandolf989]

You can also subtract one day from the date field where Sunday becomes Saturday and Monday becomes Sunday hence it would sort the days from Monday to Sunday.

select * from XYZ order by to_char(buydate-1,'D') ;