Solved

Oracle DATE Column Space

Posted on 2016-11-21
11
45 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
  • 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 31

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
 
LVL 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now