Home » SQL & PL/SQL » SQL & PL/SQL » Time Comparison (Oracle 11.2.0.3.0 Windows)
Time Comparison [message #571659] Wed, 28 November 2012 11:02 Go to next message
Duane
Messages: 380
Registered: December 2002
Senior Member
I'm hoping someone will show me the error of my way. I'm trying to compare a date and time within a certain range.

If the sysdate date/time range falls within the range of the values within the database tables then show a "Yes", otherwise, show a "No". The date works but the time doesn't seem to work. Maybe you can't use a "between" operator for time?

CREATE TABLE REGISTRATION
(
  EARLY_REGISTRATION_START_DATE  DATE,
  EARLY_REGISTRATION_END_DATE    DATE,
  EARLY_REGISTRATION_START_TIME  DATE,
  EARLY_REGISTRATION_END_TIME    DATE
)

Insert into REGISTRATION
   (EARLY_REGISTRATION_START_DATE, EARLY_REGISTRATION_END_DATE, EARLY_REGISTRATION_START_TIME, 
EARLY_REGISTRATION_END_TIME)
 Values
   (TO_DATE('11/26/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('12/03/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('01/01/1900 08:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
TO_DATE('01/01/1900 21:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;


Returns "Yes":


select case
         when (trunc(sysdate)                       between 
               trunc(early_registration_start_date) and 
               trunc(early_registration_end_date))
           then
             'Yes'
           else
             'No'
       end show_early_registration
  from registration



Returns "No":

select case
         when (trunc(sysdate)                                     between 
               trunc(early_registration_start_date)               and 
               trunc(early_registration_end_date))                and
              (to_char(sysdate, 'hh:mi AM')                       between
               to_char(early_registration_start_time, 'hh:mi AM') and
               to_char(early_registration_end_time, 'hh:mi AM'))
           then
             'Yes'
           else
             'No'
       end show_early_registration
  from registration


So, what am I doing wrong.

[Updated on: Wed, 28 November 2012 11:20] by Moderator

Report message to a moderator

Re: Time Comparison [message #571661 is a reply to message #571659] Wed, 28 November 2012 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
>So, what am I doing wrong.
It is wrong to store time in a separate column distinct from associated date column.
DATE datatype contains both date component & time component.
Re: Time Comparison [message #571663 is a reply to message #571661] Wed, 28 November 2012 11:18 Go to previous messageGo to next message
Duane
Messages: 380
Registered: December 2002
Senior Member
On my form, I have separate Date and Time fields so I just need to combine them before storing that information in the column. Then, I can use a between operator? If that's all I need to do then that's an easy change for me.
Re: Time Comparison [message #571665 is a reply to message #571659] Wed, 28 November 2012 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And Keep your lines of code in 80 character width: no more than 80 characters on each line.

Regards
Michel
Re: Time Comparison [message #571666 is a reply to message #571665] Wed, 28 November 2012 11:24 Go to previous messageGo to next message
Duane
Messages: 380
Registered: December 2002
Senior Member
I see the reformat of the code. Will do.
Re: Time Comparison [message #571669 is a reply to message #571666] Wed, 28 November 2012 11:38 Go to previous message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
> I have separate Date and Time fields so I just need to combine them before storing that information in the column.
>Then, I can use a between operator? If that's all I need to do then that's an easy change for me.
yes, BETWEEN works well for DATE datatype without any datatype conversion.
Previous Topic: how can i return only one row of data on this query?
Next Topic: Handling Exceptions!
Goto Forum:
  


Current Time: Sat Apr 19 21:12:57 CDT 2014

Total time taken to generate the page: 0.08648 seconds