Home » SQL & PL/SQL » SQL & PL/SQL » returning current weeks first date along with sysdate
icon2.gif  returning current weeks first date along with sysdate [message #194796] Mon, 25 September 2006 06:25 Go to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
hi
is it possible to write an SQL statement to return current weeks first date along with the sysdate?

i need to get the sunday s date when i compare with sysdate.

for e.x

sysdate(25-09-06)

so from the query i need output like this


week_first sysdate
---------- -------
24-09-06 25-09-06

will be glad if someone could thro some light on this.


never mind..found a solution


select decode(to_char(sysdate,'day'), 'friday', sysdate, next_day(sysdate, 'friday') - 7) from dual;

[Updated on: Mon, 25 September 2006 06:33]

Report message to a moderator

Re: returning current weeks first date along with sysdate [message #194801 is a reply to message #194796] Mon, 25 September 2006 07:01 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Your answer doesn't seemed to correspond to your question ?

If you are looking for the beginning day of the week based on the sysdate, you could use:

select trunc(sysdate,'day') from dual;


Re: returning current weeks first date along with sysdate [message #194809 is a reply to message #194796] Mon, 25 September 2006 07:47 Go to previous messageGo to next message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
TRUNC(date_val,'WW') will give you the previous Sunday. if you are looking for Monday, just add 1.

SELECT TO_CHAR(x.DAY,'DAY DD MON'), TO_CHAR(TRUNC(x.DAY,'WW') ,'DAY DD MON') FROM (
 SELECT SYSDATE-LEVEL AS DAY FROM DUAL  CONNECT BY  LEVEL<20 
) x


Gojko Adzic
http://www.gojko.com
Re: returning current weeks first date along with sysdate [message #194912 is a reply to message #194801] Tue, 26 September 2006 02:35 Go to previous message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
to ebrian:

you got me on that one.due to too much of complexities i am facing these days i tend to make things complex,thanks ebrian for making life easier
Previous Topic: I have a little problem in Oracle 10g Database For Windows
Next Topic: build a sale project for a shop?
Goto Forum:
  


Current Time: Mon Dec 05 21:30:00 CST 2016

Total time taken to generate the page: 0.06889 seconds