Search day of week in oracle

Question

to_char(sysdate,'Day') returns the current day of the week. I want to get the date last past Sunday. Of course, we can go for a complex request to do this. But is there a simple way that I don't know about?

Answer 1

You can do it with

 SELECT NEXT_DAY(SYSDATE-8, 'SUN') FROM DUAL; 

here

 SYSDATE-8 

returns a day up to 8 days &

 NEXT_DAY(mydate, 'SUN') 

returns to him next sunday

Answer 2

You can do this with such a simple request:

 SELECT dt FROM ( SELECT SYSDATE - LEVEL - 1 dt FROM DUAL CONNECT BY LEVEL < 8) WHERE TO_CHAR ( dt, 'd' ) = 1 

Answer 3

I think NEXT_DAY(SYSDATE-6, 'SUN') will work. Assuming that the current day is on Sundays, I return for 6 days (that is, the last Monday), and therefore, when I look for the next Sunday, I will receive sysdate . Whereas next_day(sysdate-8,'SUN') can be used to get the last day of the previous week. Thank you for your efforts.

Answer 4

 SELECT dt FROM ( SELECT to_date('5/23/2014') - LEVEL + 1 dt FROM DUAL CONNECT BY LEVEL < 8) WHERE TO_CHAR ( dt, 'd' ) = 1 

I changed LEVEL - 1 dt to LEVEL + 1 dt to make it work to find the previous Sunday.