Solved

Oracle DATE Column Space

Posted on 2016-11-21
11
108 Views
Last Modified: 2016-11-29
Hello Experts:

I am being asked to do the following in Oracle 11gR2:

"We jsut need enough space to write the date. Right now it is set to 1, which is not sufficient. Given that Orcale captures that 11-NOV-16, please set it for sufficient space to capture the date."

I think it is not possible to change the size of a column of datatype date.  As far as I know, the date datatype has a fixed size of seven.  I do not know what the person requiring this means by setting this column to have sufficient space to capture the date.  

The situation here is that there is data being imported into the database with what it seems to have a date format of DD-MM-YY.  Can anyone help me on this one?  If you need any further information, please let me know.  

Thanks.
0
Comment
Question by:willie0-360
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
ID: 41896882
An Oracle "date" data type is a predetermined size. You cannot alter it.

I think the person who wrote that quote may be assuming that the column is a string, perhaps varchar2

Is this a "staging table" perhaps? If so then this column might be a string so that data validation/clean-up can occur after initial import of the raw data.
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41896896
Do you mean the minimum date size? I think it would have the minimum size if the date column is 6 char, for rrmmdd.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41897503
>>We jsut need enough space to write the date. Right now it is set to 1<<
What is meant by "writing" the date? What is it that is set to 1?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 300 total points
ID: 41897803
It looks like the problem may be a date formatting problem, and not actually a date length problem.  You are correct that the actual internal length of a "date" column in Oracle is not modifiable.  But it is certainly larger than 1 byte!  I have no idea what you mean by this: "Right now it is set to 1".

Are you saying that the database "seems to have a date format of DD-MM-YY"?  Or, is it the imported data that has that format?  Or both?  

You said: "there is data being imported into the database", but you also said: "Right now it is set to 1, which is not sufficient" and: "Oracle captures that 11-NOV-16".  

We aren't clear on what data is actually being imported, or on what exactly you need to change.  Are some records getting imported, but not all?  Or, are all records getting imported, but with incorrect or truncated "date" values?  Is the time-of-day supposed to be included, or doesn't the time matter?
0
 

Author Comment

by:willie0-360
ID: 41898243
I will need to get back to our data management team.  This request is being made from the data management team, and they are the ones stating the there is not sufficient space.  I will ask them if there is any data being imported into the date column.

I understand that the date datatype has a fixed value of seven (century, year, month, day, hour, minutes, seconds).  I just wanted to confirm you all of you that I was right.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 200 total points
ID: 41898450
yes, the "data type" called "date" is a fixed length that no-one can alter

IF the data in that column is a different "data type" then perhaps length can be altered.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 300 total points
ID: 41899111
Yes, Oracle "date" columns have a fixed length that we cannot adjust.  Whether that is six bytes or seven (or some other value) is usually not an issue.  I understand that it is actually a single numeric value that is converted to a character string in whatever format is specified (either by NLS_DATE_FORMAT or by an explicit format mask and "to_char") when it is queried.

Maybe your data management team has an incomplete understanding of how Oracle stores, uses and displays "date" values.
0
 

Author Comment

by:willie0-360
ID: 41905719
Thanks a lot for your comments.  The end result was that there was an error by the party giving us the data.  They changed their column datatype on their end.  Then, when the data was imported into our database, there was a mismatch in datatype.  The datatype date is of length of seven, and it cannot be altered.
0
 

Author Closing Comment

by:willie0-360
ID: 41905721
Excellent!
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41905725
""the party giving us the data ... changed their column datatype on their end."  Yes, these things happen occasionally.  But now you have the interface working again, I presume?
0
 

Author Comment

by:willie0-360
ID: 41905760
Yes, it is working.  Our data analyst just informed me everything is working as expected.  We did not have to make any changes on our side, and this includes the date datatype.


Thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question