Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

date format

makdutakduMar 26 2012 — edited Mar 26 2012
hi all
  SELECT TRUNC (rvt.transaction_date) ,RVT.TRANSACTION_DATE  FROM rcv_vrc_txs_v rvt  WHERE
 TRUNC (rvt.transaction_date) BETWEEN (:p_from_date) AND NVL (:p_to_date,:p_from_date) 

TRUNC(RVT.TRANSACTION_DATE)                    TRANSACTION_DATE

2/5/2012                                                       2/5/2012 11:02:54 AM
2/26/2012                                                     2/26/2012 12:36:23 PM
2/29/2012                                                     2/29/2012 10:48:29 AM

in the above query when i give value for p_from_date 02-feb-2012 and p_end_date 29_feb-2012 i get the above output which is correct

but in the below query when i give  p_from_date 02-feb-2012 and p_end_date 29_feb-2012     i get  dates starting from 2007 onwards as shown below

SELECT TO_CHAR (TO_DATE (rvt.transaction_date, 'DD/MM/RRRR'), 'DD-MON-RRRR'),
       rvt.transaction_date
  FROM rcv_vrc_txs_v rvt
 WHERE TO_CHAR (TO_DATE (rvt.transaction_date, 'DD/MM/RRRR'), 'DD-MON-RRRR')
          BETWEEN (:p_from_date)
              AND NVL (:p_to_date, :p_from_date)


TO_CHAR(TO_DATE(RVT.TRANSACTION_DATE,'DD/MM/RRRR')                          TRANSACTION_DATE
13-JAN-2007                                                                                                  1/13/2007 10:32:03 AM
......                                                                                                                  .......
25-MAR-2012                                                                                                  3/25/2012 11:32:55 AM


kindly guide me to make the 2nd query also to fetch dates only from 02-feb-2012 and 29-feb-2012
thanking in advance

Edited by: makdutakdu on Mar 26, 2012 9:13 AM
This post has been answered by 908002 on Mar 26 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 23 2012
Added on Mar 26 2012
1 comment
202 views