Home » SQL & PL/SQL » SQL & PL/SQL » date time parameter (oracle 9i)
date time parameter [message #316724] Mon, 28 April 2008 01:21 Go to next message
rahulredij
Messages: 2
Registered: February 2008
Location: cHIPLUN
Junior Member

I want to extract all transactions between 7 am to 7 am of next date. Field type of Trans_date is Date in the table MM_Material_Transaction & parameter type is also date. I gave following query but it is not showing any row. Kindly guide me.


select to_char(TRANS_DATE,'DD-MON-YY HH24:MI:SS') from MM_Material_Transaction
where
TO_CHAR(TRANS_DATE,'DD-MON-YY HH24:MI:SS') BETWEEN
TO_CHAR(:P_TRANS_DATE1+(7/24),'DD-MON-YY HH24:MI:SS') and TO_CHAR(:P_TRANS_DATE2+(1+6.99/24),'DD-MON-YY HH24:MI:SS')

SELECT
to_char(SYSDATE+1/24,'DD-MON-YY HH24:MI:SS '),
TO_CHAR(:P_TRANS_DATE1+(7/24),'DD-MON-YY HH24:MI:SS '),TO_CHAR(:P_TRANS_DATE2+(1+6.99/24),'DD-MON-YY HH:MI:SS AM')
FROM DUAL
Re: date time parameter [message #316731 is a reply to message #316724] Mon, 28 April 2008 02:04 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to compare strings when you have to compare dates?
Oracle is able to use "between" with dates and it is able to make arithmeic on date: +1 is +1 day, "trunc" is the day at 0:00 and so on.

Regards
Michel
Previous Topic: Trigger help
Next Topic: Closing the cursor
Goto Forum:
  


Current Time: Sun Dec 04 10:27:36 CST 2016

Total time taken to generate the page: 0.12049 seconds