Home » SQL & PL/SQL » SQL & PL/SQL » TO_DATE Problem
TO_DATE Problem [message #183995] Mon, 24 July 2006 14:45 Go to next message
ericr
Messages: 22
Registered: January 2006
Junior Member
If I run the following:

select to_date('5/28/2006 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM') from dual

I get NO errors.

If I run the following:

select pd_finish,to_date(pd_finish,'MM/DD/YYYY HH:MI:SS AM') from tbl

I DO get errors. The hard-coded date in the first statement is in the exact format as the dates from the table in the second statement, in fact that date was copied out of the table.

Why does this work when running the first statement, but not the second? The error code is: ORA-01843

Thanks...

[Updated on: Mon, 24 July 2006 14:59]

Report message to a moderator

Re: TO_DATE Problem [message #184001 is a reply to message #183995] Mon, 24 July 2006 15:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
What data type is pd_finish? If it is date, then you should not be using to_date on it; Instead you should use to_char to display it in the format that you want or don't use anything if just using it as a date. Oracle date datatypes are stored as dates and are only formatted when output, either explicitly using to_char or by default using the value of your nls_date_format parameter.
Re: TO_DATE Problem [message #184002 is a reply to message #183995] Mon, 24 July 2006 15:11 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
No, you just think the data in that column is in the exact same format on every single row.

There is at least one row that has a different format...
Re: TO_DATE Problem [message #184005 is a reply to message #184002] Mon, 24 July 2006 17:40 Go to previous messageGo to next message
ericr
Messages: 22
Registered: January 2006
Junior Member
You make a good point. I had suspected this could be a problem. There are several thousand rows and outside of looking for a null (none) I haven't been able to figure out which value is different.

What I am trying to do is compare pd_finish (date) with sysdate to see how manys days of difference exist. I thought I needed to use to_date when I calculate the difference between pd_finish and sysdate.

Is my thinking wrong?

Thanks...
Re: TO_DATE Problem [message #184007 is a reply to message #184005] Mon, 24 July 2006 18:24 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
If pd_finish is a DATE datatype, then you certainly don't need any conversion.

Just subtract them:

(pd_finish - sysdate)


or the other way around if needed. You might want to ROUND, or TRUNC, the output.
Re: TO_DATE Problem [message #184025 is a reply to message #184007] Mon, 24 July 2006 23:11 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
round(pd_finish-sysdate,0)

Should do the trick 4 ya
Re: TO_DATE Problem [message #184062 is a reply to message #183995] Tue, 25 July 2006 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can find out which rows dont have dates in the format specified like this.
Create this function:

create or replace function valid_date (p_date   in varchar2
                                      ,p_format in varchar2 default 'dd-mm-yyyy')
                                      return varchar2 as
  v_date     date;
BEGIN
  v_date := to_date(p_date,p_format);
  return 'Y';
exception
  when others then
    return 'N';
end;

You can then use this function to tell you which rows don't contain a date in the required format:

SQL> select * from temp_date_test;

     COL_1 COL_2
---------- --------------------
         1 01/01/2000 23:14:13
         2 31/02/2000 00:00:00
         3 01/08/2000
         4 Halibut

SQL> 
SQL> select col_1,col_2
  2  from   temp_Date_test
  3  where  valid_date(col_2,'mm/dd/yyyy hh24:mi:ss') = 'N';

     COL_1 COL_2
---------- --------------------
         2 31/02/2000 00:00:00
         4 Halibut
Re: TO_DATE Problem [message #184135 is a reply to message #184062] Tue, 25 July 2006 08:45 Go to previous messageGo to next message
ericr
Messages: 22
Registered: January 2006
Junior Member
Thanks alot! Problem solved.
Re: TO_DATE Problem [message #184227 is a reply to message #183995] Tue, 25 July 2006 15:25 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Your problem might be fixed, but your table design is horrible. NEVER store a date in a varchar2 field. Store it as a DATE.
Re: TO_DATE Problem [message #184234 is a reply to message #184227] Tue, 25 July 2006 17:30 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I don't think he said what datatype it was.

(Oh unless it was JR's format checker that solved it.)

[Updated on: Tue, 25 July 2006 17:31]

Report message to a moderator

Re: TO_DATE Problem [message #184239 is a reply to message #184234] Tue, 25 July 2006 18:57 Go to previous messageGo to next message
ericr
Messages: 22
Registered: January 2006
Junior Member
William Robertson wrote on Tue, 25 July 2006 17:30

I don't think he said what datatype it was.

(Oh unless it was JR's format checker that solved it.)


The datatype is date, I mentioned that in my second post in the thread.

Thanks...
Re: TO_DATE Problem [message #184307 is a reply to message #184239] Wed, 26 July 2006 01:59 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
True, you did say
Quote:

What I am trying to do is compare pd_finish (date) with sysdate

but sometimes people say they are working with a date when they actually have a character string, so until I see some explicit mention of the DATE datatype or output of SQL*Plus DESCRIBE etc I'm never 100% sure. Then in this case both DATE and character string solutions were posted at about the same time and you said "problem solved", so I wasn't sure which you were referring to.

Anyway glad it's solved Smile
Previous Topic: Parsing XML
Next Topic: Problem with query
Goto Forum:
  


Current Time: Mon Dec 05 12:57:34 CST 2016

Total time taken to generate the page: 0.13110 seconds