Solved

Problem in Converting the date with TO_DATE function?

Posted on 2009-03-30
16
1,803 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

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.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

729 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