hi! I'm trying to make a select query which displays the entries in my table by week of month, each week beginning on a monday and ending on a sunday, with the 1st monday of the month marking the start of week 1 for the month. I used this:

SELECT * FROM table1 t WHERE EXTRACT (WEEK FROM (t.datetimestamp, 'MM DD YYYY')) =
(SELECT distinct EXTRACT(WEEK FROM (t.datetimestamp, 'MM DD YYYY'))
FROM table1 t WHERE
(EXTRACT (MONTH FROM (t.datetimestamp, 'MM DD YYYY'))= [month])
AND (to_char ((t.datetimestamp, 'MM DD YYYY'), 'W')= [week of month])
AND (EXTRACT(DOW FROM (t.datetimestamp, 'MM DD YYYY')) = [day of week])
AND (EXTRACT (YEAR FROM (t.datetimestamp, 'MM DD YYYY'))= [year]));

replacing items enclosed in [] with appropriate values, although it doesn't work for weeks when there aren't any entries for mondays. Im not sure if i should use this query and find a way to auto-generate data entries for every monday, to make sure that mondays are never null, or if i should just scrap this query and look for another way to change the date format. Any suggestions? :-)



---------------------------------
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger