RE: Force implicit data conversion

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 2 Mar 2015 13:29:10 -0500
Message-ID: <569701d05516$c7a93120$56fb9360$_at_rsiz.com>

do you mean, for example using the cast function?:

SQL> r

  1 select

  2 --+ gather_plan_statistics

  3 * from time_size

  4* where cast(timeuntrunc as date) > to_date('19700101','YYYYMMDD')  

        ID DATETRUNC DATEUNTRU TIMETRUNC

• --------- --------- ------------------------------------------------------------------------

TIMEUNTRUNC

---

         1 09-MAY-11 09-MAY-11 09-MAY-11 12.00.00.000000 AM 09-MAY-11 09.58.59.000000 AM            3 ~ 09-MAY-11 ~ 09-MAY-11 10.58.51.000000 AM            1 19-DEC-12 19-DEC-12 19-DEC-12 12.00.00.000000 AM 19-DEC-12 01.33.47.000000 AM            1 19-DEC-12 19-DEC-12 19-DEC-12 12.00.00.000000 AM 19-DEC-12 02.01.05.000000 AM     SQL> save q_cast_time

Created file q_cast_time.sql

SQL> _at_q_xplan  

PLAN_TABLE_OUTPUT

---

 

SQL_ID 9gqfbr6653j64, child number 0

---

select --+ gather_plan_statistics * from time_size where

cast(timeuntrunc as date) > to_date('19700101','YYYYMMDD')  

Plan hash value: 1526675273  

---

| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |

---

| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 4 |00:00:00.01 | 8 | |* 1 | TABLE ACCESS FULL| TIME_SIZE | 1 | 1 | 3 (0)| 4 |00:00:00.01 | 8 |

---

 

Predicate Information (identified by operation id):

---

 

   1 - filter(CAST(INTERNAL_FUNCTION("TIMEUNTRUNC") AS date)>TO_DATE(' 1970-01-01 00:00:00',

              'syyyy-mm-dd hh24:mi:ss'))  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of stephen van linge (Redacted sender "swvanlinge_at_yahoo.com" for DMARC) Sent: Monday, March 02, 2015 1:09 PM
To: Oracle-L Freelists
Subject: Force implicit data conversion  

Hi all,  

This is an academic question. Given a table A with column B (timestamp data type) and date parameter C, how can i force an implicit conversion within a select statement of B to date type?  

I'm looking at this:  

SELECT *
FROM A
WHERE B = C   and I'm not sure if B is being converted to date, or if C is being converted to timestamp...  

Thanks,  

Stephen

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 02 2015 - 19:29:10 CET