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?
 
slightwv (䄆 Netminder)Connect With a Mentor 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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
awking00Commented:
>>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 GeerlingsConnect With a Mentor Database 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
 
johnsoneConnect With a Mentor Senior 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
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.