Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem in Converting the date with TO_DATE function?

Posted on 2009-03-30
16
Medium Priority
?
1,808 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 how to recover a database from a user managed backup

670 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