select time

how can i retrieve only the time from a column in pl/sql?  my default nls_date_formant is 'dd-mon-yyyy', but when i look at the column in toad, i can see the time.  i know it's there, but how do i retrieve it?  i want to be able to say:

if <time in column> is greater than <time in sysdate>, so this.

the time in the column will look like:  31-DEC-1899 08:00:00 AM

so i can't just say

if column > sysdate because of the 31-DEC-1899 part.  i need to compare only the times. thanks.
Who is Participating?
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:
Just set the format mask of the date column to the time:

select to_char(sysdate,'HH24:MI:SS') from dual;

select to_char(sysdate,'HH24MISS') from dual;

will give you time in 24hour clock, minutes and seconds of sysdate.
lol, pipped to the post.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
After re-reading, I thought I would follow up with a little more detail:

Dates in Oracle ALWAYS contain a time part.  Just because it isn't displayed when selected from SQL*Plus (due to nls_date_format), doesn't mean it isn't there.  To get at the specific parts you use the to_char function.

If you can provide more detail on your specific requirements, I'm sure someone can provide a specific answer.

i.e./  posting the table structure and some sample data, along with expected results.

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
slightwv (䄆 Netminder) Commented:
morphman:  lol.....  great minds think alike!!!  Wasn't it you that snuck in on me yesterday?
slightwv, i may have sneaked in first yesterday, but I cant remember what it was on. Have slept since lol
@@benpung :

Don't worry about the nls_date_format. That is how the dates are displayed on the SQL Plus. So, if the format is set to  'dd-mon-yyyy', you will see the date in that format, but that does'nt mean that there is no time attached to the date. Time will not be visible if the NLS_DATE_FORMAT is set to display only the DD-MON-YYYY, but it is there ....

Let us assume the current date&time is : 10-NOV-2004 16:45:20

You will just see the date part of it on the sql plus because of the NLS_DATE_FORMAT setting. But if you change that to include the time, you would be able to see the time part also.


Now, your requirement is to compare the value (of DATE datatype) in the table with the current date and time (SYSDATE), so without bothering about the NLS_DATE_FORMAT, you could actually check the column against SYSDATE. The column in the table will have the date and time parts in it, same as the SYSDATE.

So, if you have a where clause like 'column > sysdate' , it will compare the date as well as time in the SYSDATE.

Hope that clarifies the situation.
You could use a datatype conversion like to_char from the column you need to compare and extract the time part of it, and the time part of sysdate, then convert that char to date with a to_date function, now the date part will be the same for both values, so you can compare now the time part only.

select to_date(to_char(some_column,'hh24:mi:ss'),'hh24:mi:ss'), to_date(to_char(sysdate,'hh24:mi:ss'),'hh24:mi:ss') sysdate_  from some_table

This can be done in pl/sql too.
johnsoneSenior Oracle DBACommented:
Is this what you are looking for?

if to_date(to_char(sysdate),'mmddyyyy') || to_char(<col>, 'hh24miss'),'mmddyyyyhh24miss') > sysdate then ...

Basically, you are converting the time in the column using the date in sysdate into an Oracle date and then comparing it to the current date/time.
johnsoneSenior Oracle DBACommented:
Sorry, too many )

if to_date(to_char(sysdate,'mmddyyyy') || to_char(<col>, 'hh24miss'),'mmddyyyyhh24miss') > sysdate then ...
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.

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.