CODEWITHSUNDEEP
sqloracle-database
venkatKA
venkatKA

Reputation: 2399

Finding day of the week in oracle

to_char(sysdate,'Day') returns the current day of the week. What I want is to get the date of the most recent "sunday" that passed. Of course we can go for a complex query to get it done. But is there any simple way I'm not aware of?

Upvotes: 4

Views: 18228

Answers (4)

user3670021
user3670021

Reputation: 1

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 get it to work to find the previous Sunday.

Upvotes: 0

venkatKA
venkatKA

Reputation: 2399

I think NEXT_DAY(SYSDATE-6, 'SUN') would work. Assuming current day as sunday, I go back 6 days (i.e. last monday) and so when I look for the next sunday, I'd get the sysdate itself. Whereas, next_day(sysdate-8,'SUN') could be used to get the last day of previous week. Thanks for your efforts.

Upvotes: 0

Jo&#227;o Barreto
Jo&#227;o Barreto

Reputation: 184

You can do it with a query as simple as this:

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

Upvotes: 0

Rohit Chaudhari
Rohit Chaudhari

Reputation: 757

You can do it with

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

here 

SYSDATE-8

returns the day before 8 days &

NEXT_DAY(mydate, 'SUN')

returns the next sunday to it

Upvotes: 8

Related Questions