khwabsheth's Avatar, Join Date: Feb 2007
Light Poster
I tried order by EXTRACT( DOY FROM birth_date ) which does the similar work which you said , but it will also give the problem for last week of the year.

I guess I have to use CASE statement and check if DOY (which gives day of year
for eg. 17 feb = 31+17 = 48 days) is greater then 259 ( that is 365 - 1 week) then one order by else another order by . But again i will get problem for leap year.

Give me if you have better suggestions.
khwabsheth's Avatar, Join Date: Feb 2007
Light Poster
It should be 359 not 259 in above post
khwabsheth's Avatar, Join Date: Feb 2007
Light Poster
Hey,

Finally I am done with my problem , this works fine in postgresql. But it will not work in mysql as mysql doesnt support "::" .

$strSql = "SELECT * FROM admin.employees,(SELECT to_char((to_char(now(),'yyyy')||'-12-31')::date,'ddd')::int as div) foo WHERE (to_char(birth_date,'ddd')::int-to_char(now(),'ddd')::int+div)%div BETWEEN 0 and 7 ORDER BY (to_char(birth_date,'ddd')::int-to_char(now(),'ddd')::int+div)%div;";

Enjoy!

Khwab
pradeep's Avatar, Join Date: Apr 2005
Team Leader
Cool, i will try it in MySQL