Solved

Oracle, selecting Week ending date

Posted on 2013-11-06
11
531 Views
Last Modified: 2013-11-18
to_char(to_date(xdate, 'yyyymmdd'), 'iw') 

Open in new window


The above gives me the week of the year,

Is there a way to give me that ending saturday?

lets say results of week 01, i would rather it be, instead of week 01, the first satuday of the year.

any date format would be fine
0
Comment
Question by:FutureDBA-
  • 2
  • 2
  • 2
  • +3
11 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39629230
I think you need to use NEXT_DATE together with TRUNK:

select next_date(trunc(sysdate, 'year'), 'saturday') from dual;

Open in new window


Trunc will give you the first day of the year, and the NEXT_DATE will give you the first Saturday
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39629241
try this one

, ceil((trunc(xdate) - next_day(trunc(xdate,'yyyy')-1,'saturday'))/7)+1 AS week_number

from that last day of last year, find the next Saturday = first Saturday of this year
get the difference between that date and the xdate value  / 7
treat that result as an integer (using CEIL() )
add 1
= week numbers starting at first Saturday of the year


I prepared some tests on this a while back, see this:
http://sqlfiddle.com/#!4/233ec/17
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39629246
oops not week numbers, sorry.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39629567
Here is the way to show the relevant Saturday for one date given. You do not need to care about which week it is.
select to_date('20131003', 'yyyymmdd')+DECODE(substr(upper(TO_CHAR(TO_DATE('20131003', 'YYYYMMDD'),'day')),1,3),'SAT',0,'FRI',1,'THU',2,'WED',3,'TUE',4,'MON',5,6) from dual;

Open in new window

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39630135
Or, like this:

select next_day(to_date(to_number(to_char(to_date(xdate,'yyyymmdd'), 'iw') * 7),'ddd') -7,'Sat') "WeekEnding"
from [your_table]

Note: You want want to use "w" instead of "iw" depending on how you want to treat the first partial week of the year for years when Jan. 1 is not a Sunday.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39630404
the first post is almost correct,  it needs to subtract one day, otherwise, if January 1st is a Saturday, it'll return the 2nd Saturday of the year

also the correct function is next_day, not next_date

select next_day(trunc(sysdate, 'year')-1, 'saturday') from dual;

or, using your xdate string...

next_day(trunc(to_date(xdate, 'yyyymmdd'),'yyyy')-1,'Saturday')
0
 

Author Comment

by:FutureDBA-
ID: 39631808
this is great, but i need this to happen for all weeks of the year. i want to select all saturdays of the year,

above statement only gives me the first saturday, i may have not worded correctly what i was trying to do.

thanks
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39631817
The suggestion that I gave you earlier (ID: 39630135) will work for any date in your table.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39631825
yeah,  all Saturdays is definitely different than just one Saturday


    SELECT NEXT_DAY(TRUNC(SYSDATE, 'yyyy') - 1, 'Saturday') + ((LEVEL - 1) * 7)
      FROM DUAL
CONNECT BY TO_CHAR(NEXT_DAY(TRUNC(SYSDATE, 'yyyy') - 1, 'Saturday') + ((LEVEL - 1) * 7), 'yyyy') =
               TO_CHAR(SYSDATE, 'yyyy')

or, using your variable



    SELECT NEXT_DAY(TRUNC(to_date(xdate, 'yyyymmdd'), 'yyyy') - 1, 'Saturday') + ((LEVEL - 1) * 7)
      FROM DUAL
CONNECT BY TO_CHAR(NEXT_DAY(TRUNC(to_date(xdate, 'yyyymmdd'), 'yyyy') - 1, 'Saturday') + ((LEVEL - 1) * 7), 'yyyy') =
               TO_CHAR(to_date(xdate, 'yyyymmdd'), 'yyyy')
0
 
LVL 25

Expert Comment

by:chaau
ID: 39631837
It is not clear what do you mean. Do you want the SQL statement to return 52 (or sometimes 53) rows in a single select statement. It is possible to do using connect by prior, or using ANSI cte syntax
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39632535
Try this to list out all Saturdays
  SELECT *
  FROM (    SELECT TRUNC (TO_DATE ('20130101', 'YYYYMMDD'), 'YYYY') + LEVEL - 1
                      dt
              FROM DUAL
        CONNECT BY LEVEL <=
                        ADD_MONTHS (
                           TRUNC (TO_DATE ('20130101', 'YYYYMMDD'), 'YYYY'),
                           12)
                      - 1
                      - TRUNC (TO_DATE ('20130101', 'YYYYMMDD'), 'YYYY'))
WHERE TO_CHAR (dt, 'fmDAY') = 'SATURDAY';

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
having some issue on pl sql procedure 1 29
Oracle DB Slows After Datapump Until Next Reboot 27 123
oracle differnce between two timestamps 5 38
Oracle Errors 11 45
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question