• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1478
  • Last Modified:

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.
0
benpung
Asked:
benpung
  • 3
  • 3
  • 2
  • +2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Just set the format mask of the date column to the time:

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

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

will give you time in 24hour clock, minutes and seconds of sysdate.
0
 
morphmanCommented:
lol, pipped to the post.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

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.
0
 
jcaceresCommented:
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.
0
 
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.
0
 
johnsoneSenior Oracle DBACommented:
Sorry, too many )

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now