Solved

Problem in Converting the date with TO_DATE function?

Posted on 2009-03-30
16
1,785 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
ID: 24017702
Try using the one below to fix it out

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

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24017754
What is the difference between the symbol?
0
 
LVL 7

Expert Comment

by:thiyaguk
ID: 24017758
use this

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

Expert Comment

by:Raja Jegan R
ID: 24017791
'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
ID: 24037578
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
ID: 24040781
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
ID: 24041010
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
ID: 24041133
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
ID: 24041142
is your data having the 4 digits for the date column ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24041150
sorry, for the YEAR in date column
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 24041182
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
ID: 24041222
Ya my data is having four digits for year column
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24047419
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
ID: 24047427
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
ID: 24047455
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
ID: 31564277
Good One
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
unable to get sorting resultset 15 68
history tablespace temp usage 2 31
Oracle sql query 7 44
PL/SQL More than one element associated with another element 7 0
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

920 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

15 Experts available now in Live!

Get 1:1 Help Now