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).
