Home » SQL & PL/SQL » SQL & PL/SQL » Date value shows 00-000-0000
Date value shows 00-000-0000 [message #440636] Tue, 26 January 2010 06:53 Go to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
One my table has date column. The field name is end_date and it is date column.
When we display the end_date with DD-MON-YYYY format, it shows as 00-000-0000.
Not sure, how it is displaying like this. When we display without any format, it display as 30-JAN-00.

SQL> select count(*) from retention
  2  where to_char(end_date,'DD-MON-YYYY') = '00-000-0000'
  3  /

  COUNT(*)
----------
     16490



SQL> select distinct end_date from retention
  2  where to_char(end_date,'DD-MON-YYYY') = '00-000-0000'
  3  /

END_DATE
---------
30-JAN-00



Is this date entry problem? If so, then how date column allowed to enter this kind of bad values?

Here is the NLS_DATE_FORMAT.

SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR

SQL> 


Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Report message to a moderator

Re: Date value shows 00-000-0000 [message #440638 is a reply to message #440636] Tue, 26 January 2010 07:04 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
What does this give:
select distinct dump(end_date) from retention
where to_char(end_date,'DD-MON-YYYY') = '00-000-0000';

Report message to a moderator

Re: Date value shows 00-000-0000 [message #440639 is a reply to message #440638] Tue, 26 January 2010 07:10 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
 

SQL> select distinct dump(end_date) from retention
  2  where to_char(end_date,'DD-MON-YYYY') = '00-000-0000';

DUMP(END_DATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 200,100,1,30,13,1,1

SQL>

Report message to a moderator

Re: Date value shows 00-000-0000 [message #440642 is a reply to message #440639] Tue, 26 January 2010 07:53 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
When I insert a date of 1/30/2000 into a table I get this output:
SQL> insert into xlog values ('3',to_datE('30-jan-2000 12:00:00','dd-mon-yyyy hh24:mi:ss'));

1 row created.

SQL> select dump(TIMESTAMP) from xlog;

DUMP(TIMESTAMP)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,100,1,30,13,1,1

Report message to a moderator

Re: Date value shows 00-000-0000 [message #440650 is a reply to message #440636] Tue, 26 January 2010 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Is this date entry problem? If so, then how date column allowed to enter this kind of bad values?

Yes.
With an OCI-based program that stores dates in binary.

Regards
Michel

Report message to a moderator

Re: Date value shows 00-000-0000 [message #440664 is a reply to message #440650] Tue, 26 January 2010 09:25 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Michel,

okay. I understand. But does it interpret correctly when we run the query? 

SQL>  select case_id, geography_id, start_date, to_char(start_date,'mm-dd-yyyy'),
  2   end_date, to_char(end_date, 'mm-dd-yyyy')
  3   from retention
  4   where case_id = 141
  5  /

   CASE_ID GEOGRAPHY_ID START_DAT TO_CHAR(ST END_DATE  TO_CHAR(EN
---------- ------------ --------- ---------- --------- ----------
       141           51 14-MAR-03 03-14-2003 30-JAN-00 00-00-0000
       141           52 01-JAN-00 01-01-1900 31-DEC-99 12-31-9999


In the above output, i am trying to display the record which is stored in binary format... the below query is not displaying...

SQL> select case_id, geography_id, start_date, to_char(start_date,'mm-dd-yyyy'),
  2  end_date, to_char(end_date, 'mm-dd-yyyy')
  3  from retention
  4  where case_id = 141 and end_date = to_date('30-JAN-00');

no rows selected

SQL> 


  1  select case_id, geography_id, start_date, to_char(start_date,'mm-dd-yyyy'),
  2  end_date, to_char(end_date, 'mm-dd-yyyy')
  3  from EPPICME.case_geography
  4* where case_id = 141 and end_date = to_date('30-JAN-2000')
SQL> /

no rows selected

SQL>


The below query is giving the result... since i am using the binary values...
The above query is not displaying since i am using actual values..
How do we display the records by using actual date values in the where clause?

SQL>  select case_id, geography_id, start_date, to_char(start_date,'mm-dd-yyyy'),
  2   end_date, to_char(end_date, 'mm-dd-yyyy')
  3   from EPPICME.case_geography
  4  where case_id=14 and to_char(end_date,'DD-MON-YYYY')='00-000-0000';

   CASE_ID GEOGRAPHY_ID START_DAT TO_CHAR(ST END_DATE  TO_CHAR(EN
---------- ------------ --------- ---------- --------- ----------
        14           50 14-MAR-03 03-14-2003 30-JAN-00 00-00-0000

SQL>

Report message to a moderator

Re: Date value shows 00-000-0000 [message #440667 is a reply to message #440664] Tue, 26 January 2010 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> to_date('30-JAN-00')

You should ALWAYS supply a format mask with TO_DATE() function!

Report message to a moderator

Re: Date value shows 00-000-0000 [message #440669 is a reply to message #440667] Tue, 26 January 2010 09:47 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
I used format mask.. eventhen, it is not wokring..

As per Michel, it might have stored in binary form...But it should interpret correctly..

SQL> ;
  1  select case_id, geography_id, start_date, to_char(start_date,'mm-dd-yyyy'),
  2  end_date, to_char(end_date, 'mm-dd-yyyy')
  3  from EPPICME.case_geography
  4* where case_id = 141 and end_date = to_date('30-JAN-00','DD-MON-YY')
SQL> /

no rows selected

SQL>

Report message to a moderator

Re: Date value shows 00-000-0000 [message #440671 is a reply to message #440664] Tue, 26 January 2010 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
But does it interpret correctly when we run the query?

I don't know what you can mean with "interpret correctly " for an invalid date.
All invalid dates are returned as 00/00/0000 by design and for names JAN[UARY] for MON[TH].

Regards
Michel

[Updated on: Tue, 26 January 2010 10:16]

Report message to a moderator

Re: Date value shows 00-000-0000 [message #440672 is a reply to message #440636] Tue, 26 January 2010 10:16 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Basically you're going to have to find the bit of code that inserts the invalid date, fix that, then update all the invalid dates in the database to valid ones.

Report message to a moderator

Re: Date value shows 00-000-0000 [message #440695 is a reply to message #440672] Tue, 26 January 2010 11:14 Go to previous message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Cookimonster, Michel , Thanks for your inputs. I am good now. Thank you again!!!

Report message to a moderator

Previous Topic: Pivot Table
Next Topic: MAX(DECODE) to make value (row) as column header
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Dec 07 03:16:10 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.