Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to compare date and time separately (Oracle 9.2, Unix)
Procedure to compare date and time separately [message #601613] Fri, 22 November 2013 23:30 Go to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
Hi All,

I have the below logic to compare the date and time separately.
If current_date = DOG_Date and current time is less than 11.59pm
     Set ARD date to current date + 1 
     Set ARD time to 08:00am
End if;

If current date greater than DOG Date and current time is less than 07.00am
     Set ARD date to  next Day (Can be same day)
     Set ARD time to 08:00am
End if;


The ARD format should be dd.mm.yyyy The ARD time should be 24hh.mi

Can you please help me with the PLSQL implementation of this? I tried and able to validate date part successfully, however finding problem with time part.

Thanks in advance,
Regards,

Re: Procedure to compare date and time separately [message #601617 is a reply to message #601613] Sat, 23 November 2013 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select sysdate, trunc(sysdate) cur_day, to_char(sysdate,'dd.mm.yyyy') cur_day_formatted,
  2         to_char(sysdate,'hh24.mi') cur_time_formatted
  3  from dual;
SYSDATE             CUR_DAY             CUR_DAY_FO CUR_T
------------------- ------------------- ---------- -----
23/11/2013 07:11:58 23/11/2013 00:00:00 23.11.2013 07.11
Re: Procedure to compare date and time separately [message #601636 is a reply to message #601617] Sat, 23 November 2013 10:32 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
it pays to have a look at the manuals. There is one on date formats and how to exploit Oracle's natural date math functions. As was already shown trunc() will be useful to you. Run these from SQL and see what you get.

sysdate
trunc(sysdate)
trunc(sysdate)+1-(1/24/60)

?? you can figure out how to generate 7am right ??

trunc(sysdate,'yy')
trunc(sysdate,'mm')
trunc(sysdate,'dd')
trunc(sysdate,'hh')
trunc(sysdate,'mi')


Now that you see how useful this is, have a go at reading the rest of the stuff on date formats.

I question too if you need to use PL/SQL at all. A crutch in situations like this and will slow down your queries considerably.

Kevin
Previous Topic: FIND AGE
Next Topic: dump the result of SELECT query in ARRAY
Goto Forum:
  


Current Time: Fri Mar 29 03:49:33 CDT 2024