Finding the date of the Friday in a week

[t16turbo]

Hi there,

I have a date field that I need to return in some SQL, but rather than return the date, I need to return the Friday of the week that the date is in.

does that make sense?

eg.
Date field = 18-Oct-2005
display 21-Oct-2005

thanks in advance

 

[lewisp]

Look at the NEXT_DAY function.

 

[sem]

Assuming a week starts from Sunday in your country, you may use:

select trunc(sysdate,'D') + 5 from dual

Regards, Dima

http://seminihin.narod.ru

 

[Beilstwh]

1* select next_day(sysdate,'fri') from dual
SQL> /

NEXT_DAY(
---------
21-OCT-05

SQL> select sysdate from dual;

SYSDATE
---------
18-OCT-05

Bill
Oracle DBA/Developer
New York State, USA

 

[sem]

I'm sorry, but how about NEXT_DAY(<saturday>)?

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

And I'm sorry, Sem, but what do you mean?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

I'm about using NEXT_DAY for finding "the Friday of the week that the date is in". Lewisp and Beilstwh suggested to utilize this function, but IMHO it returns Friday of the next week if being asked on Saturday, doesn't it?

Regards, Dima

http://seminihin.narod.ru

 

[lewisp]

It depends upon the definition of 'week'. The usual assumption is a working week, and even then does a week start on Saturday, Sunday or Monday?

This is where tight requirement specifications are needed. If your week starts on a Sunday you can use

[tt]Next_Day(<date> - 2,'FRI')[/tt]

 

[sem]

As for working week Saturday obviously doesn't belong to any, thus "the Friday of the week that the date is in" is undefined .

I can not find really NLS-independent solution ('FRI' is language specific and week may start from different days), or at least it would be too comlex, but for all English-speaking countries this should work:

select trunc(<date>, 'D') + to_char(Next_Day(sysdate,'FRI'),'D') - 1 from dual

Of course when a solution is required only for specific country, any of the above suits.

Regards, Dima

http://seminihin.narod.ru

 

[t16turbo]

thanks guys, i've used the next day, as the friday is the week end in our Clarity project panagement system.

I have another problem though.
the date is being returned with a time component as well.
how can I strip this off so it only shows the date (for grouping purposes)

thanks,

 

[sem]

Use TRUNC function.

Regards, Dima

http://seminihin.narod.ru

 

[lewisp]

If it is showing the time then you can use TO_CHAR(<date>,'DD-MON-YYYY') or whatever format you require.