OraFAQ Forum: SQL & PL/SQL » Date format error

Home » SQL & PL/SQL » SQL & PL/SQL » Date format error (Oracle 10g)

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

Date format error [message #611749]

Tue, 08 April 2014 08:05

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Why it is not throwing error even if the date format is incorrect.

DECLARE
l_var varchar2(20):='14-04-08';
l_date date;
BEGIN
l_date :=to_date(l_var,'YYYY-MM-DD');
--l_date :=to_date(l_var,'YY-MM-DD');
--l_date :=to_date(l_var,'RR-MM-DD');
DBMS_OUTPUT.PUT_LINE('XX'||l_date);
END;

Report message to a moderator

Re: Date format error [message #611750 is a reply to message #611749]

Tue, 08 April 2014 08:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It defaults to valid date.

SQL> DECLARE
l_var varchar2(20):='14-04-08';
l_date date;
BEGIN
l_date :=to_date(l_var,'YYYY-MM-DD');
--l_date :=to_date(l_var,'YY-MM-DD');
--l_date :=to_date(l_var,'RR-MM-DD');
DBMS_OUTPUT.PUT_LINE('XX'||l_date);
END;  2    3    4    5    6    7    8    9  
 10  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
XX08-APR-14

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Date format error [message #611751 is a reply to message #611749]

Tue, 08 April 2014 08:22

flyboy
Messages: 1903
Registered: November 2006

Senior Member

When everything else fails, it is always time to open the documentation. For Oracle database, it is available e.g. online on http://tahiti.oracle.com/

For 10gR2, the relevant chapter is placed here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
Concentrate on the notes about FX modifier (which you did not use).

So, the date format is correct - you are 20 centuries back in the year 0014 without specifying its leading zeroes.

SQL> with x as ( select to_date('14-04-08','YYYY-MM-DD') d from dual )
  2  select to_char( d, 'dd.mm.yyyy' ) actual_date from x;

ACTUAL_DAT
----------
08.04.0014

Report message to a moderator

Re: Date format error [message #611753 is a reply to message #611750]

Tue, 08 April 2014 08:48

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Then What to do so that it should throw error .

Report message to a moderator

Re: Date format error [message #611755 is a reply to message #611753]

Tue, 08 April 2014 08:59

flyboy
Messages: 1903
Registered: November 2006

Senior Member

sss111ind wrote on Tue, 08 April 2014 15:48

Then What to do so that it should throw error .

What about using the modifier I noted? It will fail when days and months will not be left padded with zeroes then as well.
What about checking whether the date is in the correct century(ies) and raise exception if it is not?
It depends why you assume that value incorrect.

Report message to a moderator

Re: Date format error [message #611756 is a reply to message #611755]

Tue, 08 April 2014 09:04

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Thank you all I got that FX specifier you are suggesting.

Regards,
Nathan

Report message to a moderator

Previous Topic:	How to get last two max values
Next Topic:	Avoiding duplicate SQL code

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:31:03 CDT 2024