Thanks a lot!!! -----Original Message----- Sent: Saturday, March 29, 2003 1:09 AM To: Multiple recipients of list ORACLE-L
Hello! Sesi Odury wrote: > Given a week between (1 - 52) for a particular year can we get all the > dates within that week. Is there a function to do this in SQL??? Using the simple statement below you can get the first date of the week (according to ISO standard). Then you can either add 6 to get the last day of the week and use ranges for your task or using any 'pivot'-approach (you need 7 rows) you can get all 7 days/dates of the week. DEFINE yr=1998 DEFINE wk=5 SELECT TRUNC(TO_DATE('2711&yr', 'DDMMYYYY'), 'IYYY') + (&wk - 1) * 7 AS date_from , TRUNC(TO_DATE('2711&yr', 'DDMMYYYY'), 'IYYY') + (&wk - 1) * 7 + 6 AS date_to FROM sys.dual / Ranges can be used in case one does not have a possibility to use FBI, for example. The statement below is a bit more complicated. This one does a simple check and returns nothing in case week number is out of range. DEFINE yr=1998 DEFINE wk=53 SELECT TRUNC(TO_DATE('2711&yr', 'DDMMYYYY'), 'IYYY') + (&wk - 1) * 7 AS date_from , TRUNC(TO_DATE('2711&yr', 'DDMMYYYY'), 'IYYY') + (&wk - 1) * 7 + 6 AS date_to FROM sys.dual WHERE TO_NUMBER( TO_CHAR( TO_DATE('3112&yr', 'DDMMYYYY') + DECODE(TO_CHAR(TO_DATE('3112&yr', 'DDMMYYYY'), 'IW') , '01', -7 , 0 ) , 'IW' ) ) >= &wk AND &wk > 0 / HTH[, if I did not make a mistake]. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sesi Odury INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).