Possible existence of time in a date-defined column

Can, by some crazy chance, an Oracle 9i table-Column that's defined as date data-type, with length of 8 bits, contain a time value? If possible, how would you extract such value.
Roberto Madro R.Programmer AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
All Oracle DATE data types have a time portion in all releases.  There is no TIME data type in Oracle.

You access it with TO_CHAR or be setting NLS_DATE_FORMAT to what you want the 'default' format to be.

select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') from dual;
0
Roberto Madro R.Programmer AnalystAuthor Commented:
I posed the question because I was getting error ORA-01481: Invalid number format model. I'm still getting the same error.
0
slightwv (䄆 Netminder) Commented:
It sounds like you are trying to do implicit data type conversions by letting Oracle guess at the string to date conversion.

Never do this.  Always use explicit data type conversion using TO_DATE.

For example:
select to_date('01/01/2001 11:22:33','MM/DD/YYYY HH24:MI:SS') from dual;


If you can post more about what you are doing, we can be more exact in our answers.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
To add:
Out of the box Oracle's default 'format' for dates is DD-MON-YY.

so:  select sysdate from dual;
returns:  14-FEB-18.

You can override that at a system/session level by setting NLS_DATE_FORMAT:
alter session set NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS';

Now when you do: select sysdate from dual;
you get:  02/14/2018 14:13:57

To go from string to date without explicitly providing the format mask, the STRING MUST MATCH the current format EXACTLY.

That is why you should ALWAYS explicitly provide the format mask you want at the time you want it with TO_DATE and TO_CHAR.  Then there is no guessing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
>>I posed the question because I was getting error ORA-01481: Invalid number format model. I'm still getting the same error.<<
What was the statement you executed that resulted in that error?
0
Roberto Madro R.Programmer AnalystAuthor Commented:
I was running Select To_Char(myColumn, 'MM/DD/YYYY HH24:MI:SS') but without altering the session & defining the NLS_DATE_FORMAT, I overcame that error.
0
slightwv (䄆 Netminder) Commented:
Make sure myColumn is an actual DATE data type and not a VARCHAR2 that is holding a string that looks like a date.

Describe the table to confirm.
0
Mark GeerlingsDatabase AdministratorCommented:
The "ORA-01481: Invalid number format" error is usually related to a NUMBER column or variable, not an Oracle "DATE" column or variable.

Yes, this explicit datatype conversion: "To_Char(myColumn, 'MM/DD/YYYY HH24:MI:SS')" will always avoid an error like that without altering the session or defining the NLS_DATE_FORMAT *IF* the actual column is a "DATE" datatype.
0
johnsoneSenior Oracle DBACommented:
Under the heading the documentation helps a little...

This details how Oracle stores a DATE datatype internally.
0
Roberto Madro R.Programmer AnalystAuthor Commented:
The collective feedback was invaluable, thank you experts.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.