Home » RDBMS Server » Server Utilities » Different date
Different date [message #376254] Tue, 16 December 2008 12:26 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Oracle 10.2.0.2 windows professional 2000
select col1, col_name from temp where col1 = 1234
col1  col_name
1234  0/0/0000



select col1, to_char(col_name,'MM/DD/YYYY') from temp where col1 = 1234
col1   col_name
1234   12/02/0000


Is somebody help with some clue. Why the both query giving 2 different date value for same record. Which one need to consider right?

Appreciate your valuable time....

[Updated on: Tue, 16 December 2008 12:29]

Report message to a moderator

Re: Different date [message #376257 is a reply to message #376254] Tue, 16 December 2008 13:10 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Obviously COL_NAME is not a DATE column or else you would not have a silly value such as 0/0/0000 in it.

Then you do a TO_CHAR on a character string, so you get unexpected results.

Use TO_CHAR on a DATE or NUMBER column.
Use TO_DATE on a character string.

When you expect Oracle to implicitly convert your data, you are using a shortsighted mentality and would/could/should get unexpected results.
Re: Different date [message #376260 is a reply to message #376257] Tue, 16 December 2008 13:18 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Thanks for the reply...

Here is the structure of the table temp.


SQL> desc temp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 Col1                                                NUMBER(15)
 Column_name                                         DATE


The column_name is the date column....
Re: Different date [message #376322 is a reply to message #376260] Tue, 16 December 2008 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you really have columns with mixed case?

Regards
Michel
Re: Different date [message #376326 is a reply to message #376322] Wed, 17 December 2008 00:02 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Besides,
SQL> select to_date('12/02/0000', 'dd/mm/yyyy') from dual;
select to_date('12/02/0000', 'dd/mm/yyyy') from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


SQL>

What I meant to say - the original poster's "example" is invalid.

I understand that people sometimes wish to conceal the real structure as well as data, but - why wouldn't you (shoaib123) provide some meaningful information? Because, most of what you have provided is invalid.
Re: Different date [message #376392 is a reply to message #376326] Wed, 17 December 2008 03:39 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
What calendar system are you using?

"Year zero is not used in the Gregorian calendar, nor in its predecessor, the Julian calendar. Under those systems, the year 1 BC is followed by AD 1." (Source: http://en.wikipedia.org/wiki/Year_zero)
Re: Different date [message #376465 is a reply to message #376326] Wed, 17 December 2008 07:48 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Let me unconceal the the table name and column name.


SQL> select to_char(start_date, 'MM/DD/YYYY') from loaner_act_1 where act_id = 958;

TO_CHAR(START_DATE,'MM/DD/Y
---------------------------------------------------------------------------
12/02/1900
12/02/0000


SQL> select to_date(to_char(start_date, 'MM/DD/YYYY'),'MM/DD/YYYY') from loaner_act_1 where ac
t_id = 958;
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0



no rows selected


Is it making sense for someone?

[Updated on: Wed, 17 December 2008 07:55]

Report message to a moderator

Re: Different date [message #376466 is a reply to message #376465] Wed, 17 December 2008 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, that's better to have real things.
Now post "select dump(start_date)..."

Regards
Michel
Re: Different date [message #376468 is a reply to message #376466] Wed, 17 December 2008 07:57 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

SQL> select dump(start_date) from loaner_act_1 where act_id = 958;

DUMP(START_DATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 119,100,12,2,1,1,1
Typ=12 Len=7: 100,100,12,2,1,1,1


Re: Different date [message #376475 is a reply to message #376468] Wed, 17 December 2008 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle correctly converts the date to string with to_char but as the second one is an invalid date, it returns an error when you want to convert it back to an internal date.
Expected behaviour.

Regards
Michel

[Updated on: Wed, 17 December 2008 08:26]

Report message to a moderator

Re: Different date [message #376480 is a reply to message #376475] Wed, 17 December 2008 08:55 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Ok thnks MIke for you reply..

I have changed the invalid portion year of date value to 1900. Just to make it as valid year..

select ADD_MONTHS(start_date,22800)  
from loaner_act_1 
where act_id = 958 and to_char(start_date,'YYYY') = '0000';

ADD_MONTHS
----------
12/02/1900


Now easily change the string value back to date format, without error.

select to_date(to_char(ADD_MONTHS(start_date,22800),'MM/DD/YYYY'),'MM/DD/YYYY')  
from loaner_act_1 
where act_id = 958 and to_char(start_date,'YYYY') = '0000';

TO_DATE(TO
----------
12/02/1900


I also found this wired day value in one date column of the table, and also wanted to make it as valid date

SQL> select to_char(start_DATE,'MM/DD/YYYY') 
from loaner_act_1 
where to_char(START_DATE,'DD') = '00'

TO_CHAR(START_DATE,
---------------------------------------------------------------------------
01/00/5267
01/00/5267


I need to change the day value "00" to "01", just to make date value as valid.

i have tried several functions like round, next_day, last_day etc but no luck. is somebody has any clue, how to do it..

[Updated on: Wed, 17 December 2008 10:13] by Moderator

Report message to a moderator

Re: Different date [message #376495 is a reply to message #376480] Wed, 17 December 2008 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Starting from an invalid date, the behaviour is unpredictable (a priori) as it depends on how variables are declared in Oracle internal code.
You have to make many many tests to find which function(s) to use and it surely depends on the invalid value.

The best way is to download in file the rows, fix the values in the file and reload it.

Regards
Michel
Re: Different date [message #376496 is a reply to message #376495] Wed, 17 December 2008 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:910228941137#465666800346698122 and following to see an example of the investigation you have to do.

Regards
Michel
Re: Different date [message #376550 is a reply to message #376496] Wed, 17 December 2008 16:10 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Thanks for the reply...

I am trying to do a work around to fix it, but ran into another problem.

I created this view.. trying to change the "DD" value '00' to '01'. View create perfectly ok..
create or replace view loaner_act_v  as
select
 case
 when to_char(START_DATE,'DD') = '00' then 
    to_date(to_char(START,'MM/')||to_char(01)||to_char(START_DATE,'/YYYY'),'MM/DD/YYYY')
 end as START_DATE
  from LOANER_ACT_1 a
  where to_char(START_DATE,'DD') = '00';


But when i query the view, it gives me the following error...

SQL> select *  from LOANER_ACT_V;
select *  from LOANER_ACT_V
                          *
ERROR at line 1:
ORA-01843: not a valid month


Surprisingly for me, when i run the select statement outside the view serprately. it executes perrfectly fine and gives me the right result.

SQL> select  case
  2   when to_char(START_DATE,'DD') = '00' then 
to_date(to_char(START_DATE,'MM/')||to_char(01)||to_char(START_DATE,'/YYYY'),'MM/DD/YYYY')
  3   end as START_DATE
  4    from LOANER_ACT_1 a
  5    where to_char(START_DATE,'DD') = '00';

START_DATE
----------
01/01/5267
01/01/5267


NLS_DATE_FORMAT                MM/DD/YYYY


I am trying to get the same result after quering the view.

I tried hard to worked around this error by changing the date format, nls_date_format and bunch of other stuffs etc. but not luck. Please help with little clue to get out of this problem... I appreciate you time..

[Updated on: Wed, 17 December 2008 23:19] by Moderator

Report message to a moderator

Re: Different date [message #376579 is a reply to message #376550] Wed, 17 December 2008 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You have to make many many tests to find which function(s) to use and it surely depends on the invalid value.

The best way is to download in file the rows, fix the values in the file and reload it.

Regards
Michel
Re: Different date [message #376828 is a reply to message #376579] Thu, 18 December 2008 13:53 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

here is what i was looking for.. Just posting this for future visitors..

 y_char := to_char(i_date, 'YYYY');
  m_char := to_char(i_date, 'MM');
  d_char := to_char(i_date, 'DD');
  if i_date is null then
    s_date := null;
  elsif y_char = '0000' then
    s_date := add_months(i_date, 22800);
  elsif m_char = '00' then
    s_date := to_date(replace(rpad(extract(month from i_date), 2, 1) || '/' ||
                              lpad(extract(day from i_date), 2, 0) || '/' ||
                              extract(year from i_date),
                              '-',
                              ''),
                      'MM/DD/YYYY');
  elsif d_char = '00' then
    s_date := to_date(replace(lpad(extract(month from i_date), 2, 0) || '/' ||
                              rpad(extract(day from i_date), 2, 1) || '/' ||
                              extract(year from i_date),
                              '-',
                              ''),
                      'MM/DD/YYYY');
  else
    s_date := i_date;
  end if;


We can put this in the function and can call it from any sql statement.

[Updated on: Thu, 18 December 2008 14:20]

Report message to a moderator

Re: Different date [message #376892 is a reply to message #376828] Fri, 19 December 2008 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback but I hope nobody will have invalid dates in their data.
It should be great for you to find which process fills these data and fix it.

Regards
Michel
Re: Different date [message #378935 is a reply to message #376892] Fri, 02 January 2009 14:39 Go to previous message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Let's Pray, nobody will ever get into this kind of problem....
Previous Topic: SQL Loader query
Next Topic: exp related
Goto Forum:
  


Current Time: Sat Dec 03 20:25:24 CST 2016

Total time taken to generate the page: 0.04613 seconds