This content has been marked as final. Show 6 replies
Try this... works like charm...
create function get_firstday (day1 date) return date is begin_day date; begin select NEXT_DAY(day1,'MONDAY')-7 into begin_day from dual; /*select next_day(to_date(day1,'dd-mm-yyyy')-7, 'Monday') into begin_day from dual;*/ return begin_day; end;
select get_firstday('14-Oct-2012') from dual;
Wow, the use of to_date was worng!!
Thank you very much :)
Edited by: user564819 on Oct 14, 2012 2:40 PM
Thanks friend. Any other doubts...???
1 person found this helpful
select next_day(to_date(day1,'dd-mm-yyyy')-7, 'Monday')You're doing a TO_DATE function on a variable that is already a date. That's a type conversion error waiting to happen.
Hi,1 person found this helpful
Another way to get the last Monday on or before DATE dt is
As you can see, it about as convenient as your function, but it's more efficient, more robust, documented in the SQL language manual, and NLS independent.
TRUNC (dt, 'IW')
ranit B wrote:No need to select from DUAL.
select NEXT_DAY(day1,'MONDAY')-7 into begin_day from dual;
You can directly assign
begin_day := NEXT_DAY(day1,'MONDAY')-7;