to_date and to_char on varchar2(10)

[saw15]

Working with Oracle 8i.
Have two columns, start_date and end_date. Both created on a table using varchar2(10).
I need to convert these to a datetime, while loading records into the table they are loaded 'mm/dd/yyyy' from a simple text file.

When I try using to_date(field,MM/DD/YYYY) I get the error ORA-01833: month conflicts with Julian date.

When I try using to_char(field,'MM/DD/YYYY') I get the error: ORA-01722: invalid number.

I need the date and time from this field, any help is appreciated.

Thanks.

 

[Turkbear]

Hi,
Try

to_date(field,'MM/DD/YY')
like

SQL> select to_date('12/23/2002','MM/DD/YYYY') from dual;
12/23/2002

 

[saw15]

This is what I tried:

select to_date(pi_s_svc_from_date,'MM/DD/YY') as start_dt
from claim_detail, My result is: 08-MAR-02.

If you just select the field: pi_s_svc_from_date, this is the result: 03/08/2002.

The dates are incorrect, they are loaded MM/DD/YY but even when I try to_date(field,'FORMAT') is comes out with its own result set.

Ideas?

 

[rcurva]

Make sure that all data in your field in this format 'MM/DD/YYYY', there could be some data stored in 'MM/DD/YY' format. And if this is the case, you may need to add a check before you format.

This is confusing though:

select to_date(pi_s_svc_from_date,'MM/DD/YY') as start_dt
from claim_detail, My result is: 08-MAR-02.

If you just select the field: pi_s_svc_from_date, this is the result: 03/08/2002.


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[lewisp]

It doesnt matter how the dates were created in the database, all that matters is how you view it when you select it out. Dates are not stored in any particular format, but the default date format when selecting for your database is usually set by your DBA.

If pi_s_svc_from_date is defined as a DATE datatype then this will work:

SELECT To_Char(pi_s_svc_from_date,'MM/DD/YYYY')
FROM claim_detail

you will get 03/08/2002. If you want the time portion as well, use To_Char(pi_s_svc_from_date,'MM/DD/YYYY HH24:MI') for example. This will show you the date and time in 24hr format.

 

[lewisp]

Of course, to change your varchar columns to datetime you will need to use

SELECT To_Char(To_Date(pi_s_svc_from_date,'MM/DD/YYYY'),'MM/DD/YYYY HH24:MI')
FROM claim_detail

but of course this would be pointless because without a time portion in the original date the time will always be 00:00.

 

[fmorel]

Dates are not internally stored as they are displayed in sql*plus.
A date can be displayed as "08-MAR-02" but it does not mean that year is stored on 2 years.

If you want to change the way dates are displayed in SQL*PLUS, you can do:
alter session set nls_date_format='MM/DD/YYYY';
before your select.