Home » SQL & PL/SQL » SQL & PL/SQL » Convert date from MMDDCCYY to YYYYMMDD (Oracle 10g version2)
icon4.gif  Convert date from MMDDCCYY to YYYYMMDD [message #487480] Tue, 28 December 2010 03:34 Go to next message
groovekarki
Messages: 8
Registered: December 2010
Location: Kathmandu
Junior Member
I have imported data from a fixed length format file into an external table in oracle which has several date fields in the format MMDDCCYY. I want to convert this into YYYYMMDD and insert it into another table in oracle with date field. A subset of the values to be converted is given below:
DOB
01051152
10581111
08151160
01031151
05131151
01111126
01511123
11531112
08531161
06301162
05521111
06511115
Any idea anybody.......

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487481 is a reply to message #487480] Tue, 28 December 2010 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
SQL> select to_char(to_date('01051152','MMDDYYYY'),'YYYYMMDD') from dual;
TO_CHAR(
--------
11520105

1 row selected.

Regards
Michel

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487482 is a reply to message #487481] Tue, 28 December 2010 03:39 Go to previous messageGo to next message
groovekarki
Messages: 8
Registered: December 2010
Location: Kathmandu
Junior Member
The given date is not in the format MMDDYYYY. the format given is MMDDCCYY...

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487483 is a reply to message #487482] Tue, 28 December 2010 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is the difference between CCYY and YYYY?

Regards
Michel

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487484 is a reply to message #487482] Tue, 28 December 2010 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use SUBSTR to do it.

Regards
Michel

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487485 is a reply to message #487484] Tue, 28 December 2010 03:47 Go to previous messageGo to next message
groovekarki
Messages: 8
Registered: December 2010
Location: Kathmandu
Junior Member
The following
SQL> select to_char(to_date('01051152','MMDDYYYY'),'YYYYMMDD') from dual;
when run doesn't provide any output.......
when all the date fields are extracted and when i use the following command in microsoft excel
=DATE(LEFT(A2,2),MID(A2,5,2),RIGHT(A2,4))
i am able to convert it into MM/DD/YYYY format

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487487 is a reply to message #487485] Tue, 28 December 2010 03:57 Go to previous messageGo to next message
groovekarki
Messages: 8
Registered: December 2010
Location: Kathmandu
Junior Member
Using substr the 1st 2 characters that relate to MM has value > than 12, similary the next 2 characters that relate to DD has value greater than 31..... How is this data to be converted into YYYYMMDD......??????

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487496 is a reply to message #487485] Tue, 28 December 2010 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
You use substr to get rid of the CC digits. Then do the conversion like Michel showed you.

Also
groovekarki wrote on Tue, 28 December 2010 09:47
The following
SQL> select to_char(to_date('01051152','MMDDYYYY'),'YYYYMMDD') from dual;
when run doesn't provide any output.......

When you run that in sqlplus it definitely will give output. So what exactly are you doing to run it?

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487497 is a reply to message #487496] Tue, 28 December 2010 04:29 Go to previous messageGo to next message
groovekarki
Messages: 8
Registered: December 2010
Location: Kathmandu
Junior Member
using substr doesn't work becoz value in MMDD exceeds the upper limit. also I am using the following query that doesn't produce any output
SQL> select to_char(to_date(DOB,'MMDDYYYY'),'YYYYMMDD') from table_name;

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487498 is a reply to message #487497] Tue, 28 December 2010 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does it give an error?

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487501 is a reply to message #487498] Tue, 28 December 2010 04:44 Go to previous messageGo to next message
groovekarki
Messages: 8
Registered: December 2010
Location: Kathmandu
Junior Member
I am using Third party appliation SQL Tools 1.5 to run my queries when i run the query there is just no output....

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487503 is a reply to message #487501] Tue, 28 December 2010 04:46 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll have sqlplus installed. Use that instead.

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487504 is a reply to message #487501] Tue, 28 December 2010 04:54 Go to previous messageGo to next message
groovekarki
Messages: 8
Registered: December 2010
Location: Kathmandu
Junior Member
using sql plus when i use this command
select to_char(to_date('11511151','MMDDYYYY'),'YYYYMMDD') from dual;
I get the following error
select to_char(to_date('11511151
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487505 is a reply to message #487480] Tue, 28 December 2010 04:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
Senior Member
groovekarki wrote on Tue, 28 December 2010 01:34

I have imported data from a fixed length format file into an external table in oracle which has several date fields in the format MMDDCCYY. I want to convert this into YYYYMMDD and insert it into another table in oracle with date field.


Dates are not stored in any particular format in Oracle. Dates are formatted using to_char when you display them. So, if your tables truly contain date columns, not character columns, then there is no conversion to be done, just select from your external table and insert into the other table. You can also change the default format by setting the nls_date_foramt. I have provided a demo below, using your data. Some of the data is rejected because it does not match your input format. For example, 58 is not a valid day. I suspect that your input format may not be what you think it is.

SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> create table external_table
  2    (dob  date)
  3  ORGANIZATION external
  4    (TYPE oracle_loader
  5  	DEFAULT DIRECTORY MY_DIR
  6  	ACCESS PARAMETERS
  7  	  (RECORDS DELIMITED BY NEWLINE
  8  	   FIELDS TERMINATED BY WHITESPACE
  9  	     ("DOB" CHAR(8) DATE_FORMAT DATE MASK "mmddyyyy"))
 10  	location ('test.dat'))
 11  REJECT LIMIT UNLIMITED
 12  /

Table created.

SCOTT@orcl_11gR2> select * from external_table
  2  /

DOB
-----------
05-Jan-1152
15-Aug-1160
03-Jan-1151
13-May-1151
11-Jan-1126
30-Jun-1162

6 rows selected.

SCOTT@orcl_11gR2> create table db_table
  2    (dob  date)
  3  /

Table created.

SCOTT@orcl_11gR2> insert into db_table select * from external_table
  2  /

6 rows created.

SCOTT@orcl_11gR2> select * from db_table
  2  /

DOB
-----------
05-Jan-1152
15-Aug-1160
03-Jan-1151
13-May-1151
11-Jan-1126
30-Jun-1162

6 rows selected.

SCOTT@orcl_11gR2> select to_char (dob, 'yyyymmdd') from db_table
  2  /

TO_CHAR(
--------
11520105
11600815
11510103
11510513
11260111
11620630

6 rows selected.

SCOTT@orcl_11gR2> alter session set nls_date_format = 'yyyymmdd'
  2  /

Session altered.

SCOTT@orcl_11gR2> select * from db_table
  2  /

DOB
--------
11520105
11600815
11510103
11510513
11260111
11620630

6 rows selected.

SCOTT@orcl_11gR2>



Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487530 is a reply to message #487485] Tue, 28 December 2010 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
SQL> select to_char(to_date('01051152','MMDDYYYY'),'YYYYMMDD') from dual;
when run doesn't provide any output.......

Curious, it does for me........
So post what you did like I posted it.

Regards
Michel

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487531 is a reply to message #487487] Tue, 28 December 2010 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
groovekarki wrote on Tue, 28 December 2010 10:57
Using substr the 1st 2 characters that relate to MM has value > than 12, similary the next 2 characters that relate to DD has value greater than 31..... How is this data to be converted into YYYYMMDD......??????

A month greater than 12 does not exist, a day greater tahn 31 does not exist. So these data do not exist, so there is no problem.

Regards
Michel

Report message to a moderator

Re: Convert date from MMDDCCYY to YYYYMMDD [message #487532 is a reply to message #487504] Tue, 28 December 2010 08:14 Go to previous message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
groovekarki wrote on Tue, 28 December 2010 11:54
using sql plus when i use this command
select to_char(to_date('11511151','MMDDYYYY'),'YYYYMMDD') from dual;
I get the following error
select to_char(to_date('11511151
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month

An invalid is an invalid date. Which date is 51/11/1151? I don't see it in the calendar.

Regards
Michel

Report message to a moderator

Previous Topic: Sequence in a Trigger
Next Topic: Query to retrive the data without iteration
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon May 19 00:15:25 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.