OraFAQ Forum: SQL & PL/SQL » Converting number column to date column

Home » SQL & PL/SQL » SQL & PL/SQL » Converting number column to date column

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Converting number column to date column [message #573254]

Tue, 25 December 2012 11:33

BeefStu
Messages: 184
Registered: October 2011

Senior Member

I have a partitioned table with ~50 million rows that was setup with a number(10) instead
of a date column. All the data in the table is ALWATS in this format YYYYMMDD

CREATE TABLE T1.monthly
(
  SEQ_NUM                    NUMBER(10)         NOT NULL,
  DAY_DK                     NUMBER(10)         NOT NULL
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
LOGGING
PARTITION BY RANGE (DAY_DK)
(
  PARTITION DEC_2012 VALUES LESS THAN (20130101)
    LOGGING
    NOCOMPRESS
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          8M
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
               ),
    PARTITION POST_2017 VALUES LESS THAN (20510101)
    NOLOGGING
    NOCOMPRESS
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               )
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

some sample data

 SEQ_NUM     DAY_DK
---------- ----------
       990   20121225
       991   20121225
       992   20121225
       993   20121225
       994   20121225
       995   20121225
       996   20121225
       997   20121225
       998   20121225
       999   20121225

When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.

desc  t1.mth;  ### my temorary table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEQ_NUM                                            NUMBER(10)
 DAY_DK                                             NUMBER(10)

Than when I try to alter my temp table "mth". I get an error table must be empty to change
column types.

alter table n546830.mth modify (DAY_DK date);

Next I tried making my temporary table "mth" a date column. When I an the exchange partiton
command I get the following error:

alter table t1.monthly exchange partition DEC_2012 
with table t1.mth without validation;
alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Does anybody know of an method I can use to convert a number(10) to date column and keep the informaiton in a table. Note, I dont care about HH:MM:SS as I never had that inforation to
begin with and would be happy to set that part of the date column to all zeroes "00:00:00"

Any examples would or advice on how to proceed would be appreciated. Thanks to all who answer

[EDITED by LF: fixed [code] tags]

[Updated on: Tue, 25 December 2012 12:16] by Moderator

Report message to a moderator

Re: Converting number column to date column [message #573255 is a reply to message #573254]

Tue, 25 December 2012 11:37

BlackSwan
Messages: 20108
Registered: January 2009

Senior Member

will DBMS_REDEFINITION work for you?

Report message to a moderator

Converting number column to date column [message #573256 is a reply to message #573254]

Tue, 25 December 2012 13:36

BeefStu
Messages: 184
Registered: October 2011

Senior Member

I have never used DBMS_REDEFINITION so I have to research that. Basically what I am looking to do is convert a number column to a date column on a table with a large amount of data. Thanks for the suggestion

Report message to a moderator

Re: Converting number column to date column [message #573262 is a reply to message #573256]

Wed, 26 December 2012 02:40

Michel Cadot
Messages: 54223
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And DBMS_REDEFINITION was created for this purpose.

Regards
Michel

Report message to a moderator

Re: Converting number column to date column [message #573297 is a reply to message #573254]

Wed, 26 December 2012 08:01

joy_division
Messages: 4267
Registered: February 2005
Location: East Coast USA

Senior Member

I was off for 4 days like most people, so maybe I am not understanding, but why do you need to do this. Format YYYYMMDD is perfectly fine for sorting of dates, so why do you need to convert to DATE?

Report message to a moderator

Re: Converting number column to date column [message #573300 is a reply to message #573297]

Wed, 26 December 2012 08:16

Michel Cadot
Messages: 54223
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Simply to make difference between 2 of them? To know what is one's week day? To not enter an invalid date like 20009999?
My question is rather: "why this had not be done at the beginning"? Wink

Regards
Michel

Report message to a moderator

Converting number column to date column [message #573319 is a reply to message #573300]

Wed, 26 December 2012 11:43

BeefStu
Messages: 184
Registered: October 2011

Senior Member

Michel provided part of the answer. I want to the DB to check for valid date as this table accepts multiple feeds from other systems and some times the dates are not formatted correctly, which causes issues.

This is a partitioned table and the code we use to automate partitions will only
work on date column. We don't want to have a different tool to support this table.

Lastly, there maybe a time in the future when we would want to use date arithmetic on this
column.

BTW. I testing this solution now.


ALTER TABLE new_table NOLOGGING;


INSERT /*+ APPEND */  INTO   new_table    (seq_num, day_dk)
SELECT  (seq_num, TO_DATE ( TO_CHAR (day_dk), 'YYYYMMDD' )
FROM	t1.monthly;

alter table new_table logging;

Thank you all for your input

Report message to a moderator

Re: Converting number column to date column [message #573320 is a reply to message #573319]

Wed, 26 December 2012 11:48

Littlefoot
Messages: 16993
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Though: are you SURE that all "dates" in that column follow the format you provided to the function (i.e. YYYYMMDD)? Because, 20120812 is valid, but it can be 08th of December 2012 as well as 12th of August 2012.

Report message to a moderator

Converting number column to date column [message #573460 is a reply to message #573320]

Fri, 28 December 2012 14:58

BeefStu
Messages: 184
Registered: October 2011

Senior Member

@LittleFoot,

Not sure I see your concern. If the month and day are mixed up as a number column it will
stil be mixe up as a date column.

Report message to a moderator

Re: Converting number column to date column [message #573461 is a reply to message #573460]

Fri, 28 December 2012 20:54

msol25
Messages: 211
Registered: June 2011
Location: Mumbai

Senior Member

Dear Beefu,

Before converting just apply substr function and find max number for DD,MM and YYYY.Then may be you will get some idea,

[Updated on: Fri, 28 December 2012 20:54]

Report message to a moderator

Re: Converting number column to date column [message #573470 is a reply to message #573461]

Sat, 29 December 2012 01:53

Michel Cadot
Messages: 54223
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't know for other people, but I never understand your answers and even if it is an answer of something.

Regards
Michel

[Updated on: Sat, 29 December 2012 10:25]

Report message to a moderator

Re: Converting number column to date column [message #573486 is a reply to message #573319]

Sat, 29 December 2012 10:02

BlackSwan
Messages: 20108
Registered: January 2009

Senior Member

BeefStu wrote on Wed, 26 December 2012 09:43

Michel provided part of the answer. I want to the DB to check for valid date as this table accepts multiple feeds from other systems and some times the dates are not formatted correctly, which causes issues.

This is a partitioned table and the code we use to automate partitions will only
work on date column. We don't want to have a different tool to support this table.

Lastly, there maybe a time in the future when we would want to use date arithmetic on this
column.

BTW. I testing this solution now.
ALTER TABLE new_table NOLOGGING;


INSERT /*+ APPEND */  INTO   new_table    (seq_num, day_dk)
SELECT  (seq_num, TO_DATE ( TO_CHAR (day_dk), 'YYYYMMDD' )
FROM	t1.monthly;

alter table new_table logging;

In the SELECT statement above first you convert "DAY_DK" from Date to String using TO_CHAR();
then you convert that results back to DATE using TO_DATE().
WHY?

Report message to a moderator

Re: Converting number column to date column [message #573502 is a reply to message #573486]

Sat, 29 December 2012 13:50

cookiemonster
Messages: 9165
Registered: September 2008
Location: Rainy Manchester

Senior Member

BlackSwan wrote on Sat, 29 December 2012 16:02

In the SELECT statement above first you convert "DAY_DK" from Date to String using TO_CHAR();
then you convert that results back to DATE using TO_DATE().
WHY?

I suggest you re-read the topic from the beginning. day_dk is not a date.

EDIT: fixed tags

[Updated on: Sat, 29 December 2012 13:51]

Report message to a moderator

Previous Topic:	ORA-29283: invalid file operation
Next Topic:	Extract Absent days from the month

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 00:16:03 CDT 2013

Total time taken to generate the page: 0.90376 seconds