Solved

Problem in Converting the date with TO_DATE function?

Posted on 2009-03-30
16
1,782 Views
Last Modified: 2013-12-19
Hi,
  While converting the date with TO_DATE function, it is not converting the proper year. For example this is date i am going the convert '12/31/1900'. Below is my convertion function
TO_DATE('12/31/1900','MM-DD-YYYY'). Now i am going to insert this date into one table. At that time it is inserted as '12/31/2000'. Can anyone tell why this is occured and how can i convert this?

Thanks
0
Comment
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Try using the one below to fix it out

TO_DATE('12/31/1900','MM/DD/YYYY')
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
What is the difference between the symbol?
0
 
LVL 7

Expert Comment

by:thiyaguk
Comment Utility
use this

 TO_DATE('12/31/1900','MM/DD/YYYY')
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
'MM/DD/YYYY' tells how your input string looks like. Your statement tells '-' is present in between your inputs string whereas actually it contains '/'
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Hi, Actually i am uploading the data from csv file to the table through SQL loader. If i insert manually through insert command the date column is inserted correctly like '12/31/1900'. But i am uploading from csv file. At that time it is inserting '12/31/2000'. In my control file i was using like below.
END_DATE_ACTIVE "TRIM(BOTH FROM (TO_DATE(:END_DATE_ACTIVE,'MM/DD/YYYY')))

What should i do for this?

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
can you try the below in your control file :

END_DATE_ACTIVE "TRIM(BOTH FROM (TO_DATE(:END_DATE_ACTIVE,'MM/DD/RRRR')))
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
No, i was using that one. It is not getting 1900 as year. Still it is inserting year as 2000.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
can you give me your complete ctl file ? and also the data which you are trying to load ( 2 sample record which have this issue )
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
is your data having the 4 digits for the date column ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
sorry, for the YEAR in date column
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Hi,
  Attached are the control and data file. I just changed the file extension. Becasue it is not allowing to add file with csv and ctl format.
SAMPLE.xls
SAMPLE.txt
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Ya my data is having four digits for year column
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
create the sample table with the below commands in the database first...

create table sample_Date
( END_DATE_ACTIVE date );

select * from sample_date;

Then the command use to run the loader...I have modified the ctl file and it works fine as expected.

C:\>sqlldr userid=mis_owner/mis_owner@lngmisd1 control=sam.ctl data=sam.csv

SQL*Loader: Release 10.2.0.3.0 - Production on Thu Apr 2 15:51:16 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 5

=======================
I have attached the sam.ctl file and sam.csv file  -- renamed to .ctl to .txt and renamed .csv to .xls because of uploading issues.
sam.xls
sam.txt
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
The "TRIM(BOTH FROM (....)" had caused the issue and hence modified it in your .ctl file and it works fine.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 250 total points
Comment Utility
when inserting data into a date column, we have to use to_date(...) .. i mean inside the to_Date(..) function we can use any string functions like TRIM etc but not outside TRIM because that is again converting the date returned by TO_DATE(..) to a character value and hence the issue. The current century is used instead of 19 when it is implicitly converted from the return value of TRIM before it gets inserted into the date column.

Hope the explanation is clear.
0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Good One
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now