 DATE problem [message #128688] |
Wed, 20 July 2005 02:55  |
shazzyncs
Messages: 12
Registered: July 2005
Location: Malaysia
|
Junior Member |
|
|
Hie everyone..i have this file in excel that i want to import to the oracle.some of the records are shown below. The problem is when i import the data.the date got changed for eg. in service end date column from '12\31\02' to '12\31\0002'.But the date in my booking_date column came out ok.I'm just wondering what's wrong with my data type. This is my control file.Anyone??Thanx a lot for ue help..
booking_date date"MM/DD/YYYY HH:MI:SS AM",service_start_date date "MM/DD/YYYY",service_end_date date "MM/DD/YYYY",
Service_Start_Date
12/31/02
12/30/02
12/30/02
12/30/02
12/29/02
12/29/02
12/28/02
Service End date
01/02/03
01/06/03
01/06/03
01/03/03
12/30/02
01/03/03
01/01/03
Booking_Date
10/29/2002 12:00:00 AM
10/29/2002 12:00:00 AM
10/29/2002 12:00:00 AM
10/29/2002 12:00:00 AM
10/29/2002 12:00:00 AM
10/29/2002 12:00:00 AM
10/29/2002 12:00:00 AM
[Updated on: Wed, 20 July 2005 02:57] Report message to a moderator |
|
|
|
| Re: DATE problem [message #128724 is a reply to message #128688] |
Wed, 20 July 2005 05:29  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
It's your date format.
SQL> select to_date('01/01/02','DD/MM/YYYY') x
2 from dual;
X
----------
01/01/0002
SQL> select to_date('01/01/02','DD/MM/RRRR') x
2 from dual;
X
----------
01/01/2002
Look up the format models in the SQL Reference.
| from the SQL Reference |
The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.
If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year. That is:
* If the specified two-digit year is 00 to 49, then
o If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
o If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
* If the specified two-digit year is 50 to 99, then
o If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
o If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.
|
MHE
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
