OraFAQ Forum: SQL & PL/SQL » Date functions

Home » SQL & PL/SQL » SQL & PL/SQL » Date functions

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date functions [message #636236]

Mon, 20 April 2015 10:36

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Hi Experts,...

declare
ab varchar2(30);
begin
select to_char(to_date('01-JAN-12','dd-mon-yy'),'dd/mm/yyyy HH24:MI:SS') INTO ab from dual;
dbms_output.put_line(ab);
end;
/

above is the query which i am able to get correct output but when i am changing ab variable data type into date format(ab date) i am getting error ..please provide me a solution for this...
Thanq

Report message to a moderator

Re: Date functions [message #636238 is a reply to message #636236]

Mon, 20 April 2015 10:38

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What error? There is no error in your post!

You assign string to string and date to date.
If you assign string to date, this is an error in itself.

Report message to a moderator

Re: Date functions [message #636239 is a reply to message #636238]

Mon, 20 April 2015 10:43

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

What error is that then?

SQL> l
  1  declare
  2  ab varchar2(30);
  3  begin
  4  select to_char(to_date('01-JAN-12','dd-mon-yy'),'dd/mm/yyyy HH24:MI:SS') INTO ab from dual;
  5  dbms_output.put_line(ab);
  6* end;
SQL> /
01/01/2012 00:00:00

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Date functions [message #636243 is a reply to message #636239]

Mon, 20 April 2015 11:33

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

If i change ab varchar2(30) to
ab date: In this scenario i am getting below error:
ORA-01843: not a valid month
ORA-06512: at line 4

Report message to a moderator

Re: Date functions [message #636244 is a reply to message #636243]

Mon, 20 April 2015 11:39

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why don't you copy and paste what you do and get just like Roachcoach did?

Just like that;

SQL> declare
  2  ab varchar2(30);
  3  begin
  4  select to_char(to_date('01-JAN-12','dd-mon-yy'),'dd/mm/yyyy HH24:MI:SS') INTO ab from dual;
  5  dbms_output.put_line(ab);
  6  end;
  7  /
01/01/2012 00:00:00

PL/SQL procedure successfully completed.

Or that:

SQL> declare
  2  ab varchar2(30);
  3  begin
  4  select to_char(to_date('01-JAN-12','dd-mon-yy'),'dd/mm/yyyy HH24:MI:SS') INTO ab from dual;
  5  dbms_output.put_line(ab);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 4

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.
Use SQL*Plus and copy and paste your session.

Report message to a moderator

Re: Date functions [message #636251 is a reply to message #636244]

Mon, 20 April 2015 11:59

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

declare
ab date;
begin
select to_char(to_date('01-JAN-12','dd-mon-yy'),'dd/mm/yyyy HH24:MI:SS') INTO ab from dual;
dbms_output.put_line(ab);
end;
/

error:
ORA-01843: not a valid month
ORA-06512: at line 4

Report message to a moderator

Re: Date functions [message #636254 is a reply to message #636251]

Mon, 20 April 2015 12:05

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do you see I did not change the variable type?
So what do you think the problem could be?

Hint:

select to_char(add_months(trunc(sysdate,'YEAR'),level-1),'MON') from dual connect by level <= 12;

And, once again, use SQL*Plus.

Also, read again my first post, it contains the answer.

Report message to a moderator

Re: Date functions [message #636262 is a reply to message #636254]

Mon, 20 April 2015 12:24

P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad

Member

Hi Michel Cadot,
First of all Thanks for the reply..
Actually i need a requirement of Timestamp like this 'dd/mm/yyyy HH24:MI:SS'
for this i just tried the above code and working fine as you see but assuming that if the variable(ab) contains date format,please provide code for this scenario
i am sorry if i say anything wrong
Note:actually this is requirement in oracle apps form which one of the column data type contains date format here i am taking column as ab

Report message to a moderator

Re: Date functions [message #636264 is a reply to message #636262]

Mon, 20 April 2015 12:33

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If your variable is a of DATE datatype then your query should return a DATE.
Like this:

SQL> declare v date;
  2  begin
  3    select sysdate into v from dual;
  4    dbms_output.put_line(to_char(v, 'DD/MM/YYYY HH24:MI:SS'));
  5  end;
  6  /
20/04/2015 19:33:01

PL/SQL procedure successfully completed.

Did you execute the query I gave?
What is its result?

Report message to a moderator