1

The following doesn't work:

set ServerOutput ON
accept anyDate DATE FORMAT 'YYYY-MM-DD' default '1982-01-24' prompt "Enter a date in this format 'YYYY-MM-DD':"
DECLARE
BEGIN
dbms_output.put_line(TO_CHAR(&anyDate, 'DD MONTH YYYY'));
END;
user272735
  • 10,473
  • 9
  • 65
  • 96
user2420374
  • 147
  • 2
  • 2
  • 13
  • possible duplicate of [SQL\*Plus how to accept text variable from prompt?](http://stackoverflow.com/questions/16674252/sqlplus-how-to-accept-text-variable-from-prompt) – user272735 Apr 08 '14 at 09:13
  • It always helps to say what actually happens - "doesn't work" isn't helpful. Pretty sure this is already answered, but the title is a little confusing; do you mean you want the user to type in the month name rather than the month number? – Alex Poole Apr 08 '14 at 09:36

3 Answers3

2

Because &anyDate will expand to a string you have to quote it:

TO_CHAR('&anyDate', 'DD MONTH YYYY')

See SQL*Plus how to accept text variable from prompt? for the details.

Here is an example that also addresses the conversion:

SQL@xe> accept foo date format 'YYYY-MM-DD' default '2000-01-01' prompt 'enter date: '
enter date: 2014-12-13

SQL@xe> select to_char(to_date('&foo', 'YYYY-MM-DD'), 'DD MONTH YYYY' ) from dual;
old   1: select to_char(to_date('&foo', 'YYYY-MM-DD'), 'DD MONTH YYYY' ) from dual
new   1: select to_char(to_date('2014-12-13', 'YYYY-MM-DD'), 'DD MONTH YYYY' ) from dual

TO_CHAR(TO_DATE('
-----------------
13 DECEMBER  2014

SQL@xe>
Community
  • 1
  • 1
user272735
  • 10,473
  • 9
  • 65
  • 96
  • Is it possible to remove the TO_CHAR(TO_DATE(' column and only keep 13 DECEMBER 2014? In other words, can I put the select to_char in a variable and use it in a dbms_output.put_line like dbms_output.put_line('The date is : ' || '&foo'); – user2420374 Apr 08 '14 at 10:15
  • Yes you can. You need to modify the code accordingly like select into from dual; then dbms_output.put_line(); – Avrajit Apr 08 '14 at 11:34
0

I CAN SUGGEST A DIFFERENT WAY.Instead of using accept and prompt you can try this snippet it may resolve your problem.

SET ServerOutput ON
--Accept anyDate DATE FORMAT 'YYYY-MM-DD' default '1982-01-24' prompt "Enter a date in this format 'YYYY-MM-DD':"
DECLARE
  DT_VAR  VARCHAR2(100);
  anydate DATE;
BEGIN
  dt_var :='&Enter_date';
  anydate:=TO_DATE(dt_var,'YYYY-MM-DD');
  DT_VAR :=TO_CHAR(anydate,'MONTH');
  dbms_output.put_line(DT_VAR);
END;
Avrajit
  • 230
  • 1
  • 2
  • 8
-1

Hi you can use something like this -

Select DateName( month , DateAdd( month , @MonthNumber , -1 ) )

Convert Month Number to Month Name Function in SQL

Community
  • 1
  • 1
Avdhey
  • 79
  • 5