Posts

Showing posts with the label varchar

Oracle Varchar2 DateTime Experassions - yyyy-mm-dd HH24:MI:SS

There is a verchar2 Date Time Filed  in the table which now need to get Results with the given rnage eg : up to 3 days of the past from today. Eg Field DONE_TIME SELECT  * substr(DONE_TIME,0,19) FROM EG_TABLE Convert to date : SELECT  *  TO_DATE(substr(DONE_TIME,0,19),'yyyy-mm-dd HH24:MI:SS') FROM EG_TABLE Reduce From the System Time : SELECT  *  (systimestamp-TO_DATE(substr(substr(DONE_TIME,0,19),0,19), 'yyyy-mm-dd HH24:MI:SS')) FROM EG_TABLE RANGE Comparison  : WHERE trunc(to_number(substr((systimestamp-TO_DATE(substr(substr(DONE_TIME,0,19),0,19), 'yyyy-mm-dd HH24:MI:SS')),1,instr(systimestamp-TO_DATE(substr(substr(DONE_TIME,0,19),0,19), 'yyyy-mm-dd HH24:MI:SS'),' '))))>3