| To get Last week ? [message #603009] |
Tue, 10 December 2013 23:46  |
 |
malli123
Messages: 6
Registered: December 2013
|
Junior Member |
|
|
Hi,
I am using the below function for getting last week.
(current_date-DAYOFWEEK(Current_Date)) +1
If i run in any date it will bring the respective last week.
Can any one please help me above function is correct ?
[MERGED by LF]
[Updated on: Thu, 12 December 2013 02:09] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: To get Last week ? [message #603019 is a reply to message #603011] |
Wed, 11 December 2013 00:37  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
select to_char(trunc(current_date, 'W') - 7, 'YYYY-MM-DD')
from dual
CURRENT_DATE is standard in Oracle. It is like SYSDATE except it uses the session timezone instead of the database timezone. I don't think Oracle has a DAYOFWEEK() function; the closest equivalent might be TO_DATE(..., 'D')
It's possible that truncating to the beginning of the week might be impacted by your locale. Some locales (France maybe?) have Monday as the start of the week; but most (according to me) seem to be Sunday. I believe this affects the results to TO_CHAR(sysdate, 'D') (day as a digit: 1-7), it might well affect TRUNC(date, 'W') as well. I leave it as an exercise for you to find out and decide whether it impacts your program.
Ross Leishman
|
|
|
|
| Re: To get Last week ? [message #603051 is a reply to message #603019] |
Wed, 11 December 2013 03:45 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Well, did you check Oracle docs? TRUNC(date, 'W') is NLS independent since it format element W assumes week 1 starts on the first day of the month and ends on the seventh. So I doubted OP wnats that. Oracle equivalent of:
(current_date-DAYOFWEEK(Current_Date)) +1
which, btw, is first day of current week nd not last week, would be:
trunc(sysdate) - to_char(sysdate,'D') + 1
And it is NLS depenedent. So client in US will get December 8 while client in France December 9:
SQL> alter session set nls_territory=America;
Session altered.
SQL> select sysdate - to_char(sysdate,'D') + 1 from dual;
SYSDATE-T
---------
08-DEC-13
SQL> alter session set nls_territory=France;
Session altered.
SQL> select sysdate - to_char(sysdate,'D') + 1 from dual;
SYSDATE-
--------
09/12/13
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to bring last week by current date ? [message #603177 is a reply to message #603170] |
Thu, 12 December 2013 01:35 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
malli123 wrote on Thu, 12 December 2013 11:35i am using (current_date-dayofweek(current_date))+1, it is working fine from monday to saturday it always going last week end date(8/12/2013). But if i run same thing on Sunday(15/12/2013) it is not going last week.
What is dayofweek? Is it a function you created? Or is it outside to Oracle where you are executing? In Oracle we do not have dayofweek function.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
