Oracle DATE Column Space

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.
willie0-360Asked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
HuaMin ChenSystem AnalystCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
awking00Commented:
>>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
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
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
 
willie0-360Author Commented:
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
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
willie0-360Author Commented:
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
 
willie0-360Author Commented:
Excellent!
0
 
Mark GeerlingsDatabase AdministratorCommented:
""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
 
willie0-360Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.