Thanks for the question, Gopi.
Asked: August 25, 2016 - 6:37 pm UTC
Last updated: August 26, 2016 - 2:14 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi ,
My aim is to print week days from a given date, for ex :
IF I provide a date like '12-DEC-2012' ( Which is Thursday ) then my program should print as below
FRIDAY
SATURDAY
But below code is running in a loop for ever, I am unable to find the error.
DECLARE
V_DATE_GIVEN VARCHAR2(20):=&V_DATE_GIVEN;
V_DATE DATE:= TO_DATE(V_DATE_GIVEN,'DD-MON-YYYY');
V_DAY VARCHAR2(50);
BEGIN
WHILE TO_CHAR(V_DATE,'DAY') != 'SUNDAY'
LOOP
V_DATE:=V_DATE+1;
DBMS_OUTPUT.PUT_LINE(V_DATE);
V_DAY:=TO_CHAR(V_DATE,'DAY');
DBMS_OUTPUT.PUT_LINE(V_DAY);
END LOOP;
END;
-------------------------------------------------
Above program gives result as like below
13-DEC-12
THURSDAY
14-DEC-12
FRIDAY
15-DEC-12
SATURDAY
16-DEC-12
SUNDAY
17-DEC-12
MONDAY
18-DEC-12
TUESDAY
19-DEC-12
WEDNESDAY
20-DEC-12
THURSDAY
21-DEC-12
FRIDAY
22-DEC-12
SATURDAY
23-DEC-12
SUNDAY
and so on-----Till Buffer space completes
Thanks
Gopi
and Connor said...
Easy fix.... the "DAY" format mask is padded, eg
SQL> select to_char(sysdate,'DAY') from dual;
TO_CHAR(SYSDATE,'DAY')
------------------------------------
FRIDAY
SQL> select 'x'||to_char(sysdate,'DAY')||'x' from dual;
'X'||TO_CHAR(SYSDATE,'DAY')||'X'
--------------------------------------
xFRIDAY x
So your TO_CHAR(V_DATE,'DAY') != 'SUNDAY' check never succeeds.
Perhaps use the "D" format mask, or a rtrim.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment