| date time parameter [message #316724] |
Mon, 28 April 2008 01:21  |
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  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
