Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1810
  • Last Modified:

Problem in Converting the date with TO_DATE function?

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
Suriyaraj_Sudalaiappan
Asked:
Suriyaraj_Sudalaiappan
  • 7
  • 6
  • 2
  • +1
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try using the one below to fix it out

TO_DATE('12/31/1900','MM/DD/YYYY')
0
 
Suriyaraj_SudalaiappanAuthor Commented:
What is the difference between the symbol?
0
 
thiyagukCommented:
use this

 TO_DATE('12/31/1900','MM/DD/YYYY')
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
'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
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
can you try the below in your control file :

END_DATE_ACTIVE "TRIM(BOTH FROM (TO_DATE(:END_DATE_ACTIVE,'MM/DD/RRRR')))
0
 
Suriyaraj_SudalaiappanAuthor Commented:
No, i was using that one. It is not getting 1900 as year. Still it is inserting year as 2000.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
is your data having the 4 digits for the date column ?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
sorry, for the YEAR in date column
0
 
Suriyaraj_SudalaiappanAuthor Commented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
Ya my data is having four digits for year column
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
The "TRIM(BOTH FROM (....)" had caused the issue and hence modified it in your .ctl file and it works fine.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Suriyaraj_SudalaiappanAuthor Commented:
Good One
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now