Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle DATE Column Space

Posted on 2016-11-21
11
Medium Priority
?
152 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 800 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 11

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 1200 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 800 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 1200 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

721 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