Home » SQL & PL/SQL » SQL & PL/SQL » Converting number column to date column
Converting number column to date column [message #573254] Tue, 25 December 2012 11:33 Go to next message
BeefStu
Messages: 192
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 Go to previous messageGo to next message
BlackSwan
Messages: 22797
Registered: January 2009
Senior Member
will DBMS_REDEFINITION work for you?
Converting number column to date column [message #573256 is a reply to message #573254] Tue, 25 December 2012 13:36 Go to previous messageGo to next message
BeefStu
Messages: 192
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
Re: Converting number column to date column [message #573262 is a reply to message #573256] Wed, 26 December 2012 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And DBMS_REDEFINITION was created for this purpose.

Regards
Michel
Re: Converting number column to date column [message #573297 is a reply to message #573254] Wed, 26 December 2012 08:01 Go to previous messageGo to next message
joy_division
Messages: 4515
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?
Re: Converting number column to date column [message #573300 is a reply to message #573297] Wed, 26 December 2012 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59165
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
Converting number column to date column [message #573319 is a reply to message #573300] Wed, 26 December 2012 11:43 Go to previous messageGo to next message
BeefStu
Messages: 192
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
Re: Converting number column to date column [message #573320 is a reply to message #573319] Wed, 26 December 2012 11:48 Go to previous messageGo to next message
Littlefoot
Messages: 19633
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.
Converting number column to date column [message #573460 is a reply to message #573320] Fri, 28 December 2012 14:58 Go to previous messageGo to next message
BeefStu
Messages: 192
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.

Re: Converting number column to date column [message #573486 is a reply to message #573319] Sat, 29 December 2012 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 22797
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?
Re: Converting number column to date column [message #573502 is a reply to message #573486] Sat, 29 December 2012 13:50 Go to previous message
cookiemonster
Messages: 10961
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:
  


Current Time: Mon Sep 22 11:49:57 CDT 2014

Total time taken to generate the page: 0.05512 seconds