-
oracle finding day of week
Is there a function I can use that can find 5 Saturdays ago from the
current date or find 8 Sundays from the current date?
What I want to do is pass do something like this:
select dow(sysdate, -7(saturday) from dual
dow = Day of week
Thanks to all who answer.
-
In most solutions they construct a permanent time/date table to handle such problems (Like holidays,working days,day of week,weeknumber,...) with dates from the past to the far future.
Finding the x-day of type z could be done by ranking futures on the date/time dimension.
-
Thanks for the response but I find it hard to believe Oracle does not
provide that functionalityor some derviation of it.
Ie
SQL> select sysdate from dual;
SYSDATE
---------
14-MAR-11
SQL> select NEXT_DAY ('01-MAR-11', 'MON') from dual;
NEXT_DAY(
---------
07-MAR-11
There has to be some way of doing this? Maybe subtracting 5 weeks from
the current date and than finding the next occurance of that day?
Any examples or input would be greatly appreciated.
-
Try this for seven Sundays in advance counting from next Sunday.
Code:
select NEXT_DAY((sysdate + 49), 'SUN') from dual;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
You can also do this:
Code:
SYS@rman AS SYSDBA> select to_char(this_date, 'day') day, this_date
from ( select trunc(sysdate, 'd')-36 this_date from dual);
DAY THIS_DATE
------------ ---------------
saturday 05-FEB-11
Elapsed: 00:00:00.10
SYS@rman AS SYSDBA> select to_char(this_date, 'day') day, this_date
from ( select trunc(sysdate, 'd')+41 this_date from dual);
DAY THIS_DATE
------------ ---------------
saturday 23-APR-11
Elapsed: 00:00:00.05
-
Perfect!! This is exactly what I was looking for. Thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|