Procedure to compare date and time separately [message #601613] |
Fri, 22 November 2013 23:30 |
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 #601636 is a reply to message #601617] |
Sat, 23 November 2013 10:32 |
|
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
|
|
|