Oracle Date and Time Range Query

Oracle Date and Time Range Query SQL code/* Date and Time Range Query */---------- create Date and Time Test table ‑ www.2cto.com create table testdatetime (ID integer not null primary KEY, createdate CHAR (10) NULL, startdate date null, enddate date null, buydate CHAR (10) NULL, usedate varchar2 (20) NULL); ------------------------ DATE and Time Range Query ------------------------------------ SELECT t. *, t. rowid from testdatetime t; -- field: string type (char), length: 10 SELECT * FROM testdatetime t WHERE 1 = 1 AND t. createdate = '2014-07-01 '; SELECT * FROM testdatetime t WHERE 1 = 1 AND t. createdate> '2014-07-01 '; SELECT * FROM testdatetime t WHERE 1 = 1 AND t. createdate <'2014-07-01 '; SELECT * FROM testdatetime t WHERE 1 = 1 AND t. createdate> = '2014-06-01 'AND
T. createdate <= '2014-07-05 '; www.2cto.com SELECT * FROM testdatetime t WHERE 1 = 1 AND to_date (t. createdate, 'yyyy-MM-dd') between to_date ('2017-06-01 ', 'yyyy-MM-dd') and to_date ('20170101', 'yyyymmdd '); ------------------------------------------------------------------- SELECT t. *, t. rowid from testdatetime t; -- field: string type (char), length: 20 SELECT * FROM testdatetime t WHERE 1 = 1 AND t. usedate = '2014-07-01 10:00:00 '; SELECT * FROM testdatetime t WHERE 1 = 1 AND t. usedate> '2014-07-01 '; SELECT * FROM testdatetime t WHERE 1 = 1 AND t. usedate <'2014-07-01 '; SELECT * FROM testdatetime t WHERE 1 = 1 AND t. usedate> = '2017-06-01 'AND t. usedate <= '2014-07-05 '; SELECT * FROM testdatetime t WHERE 1 = 1 AND to_date (t. usedate, 'yyyy-MM-dd HH24: MI: ss ')
Between to_date ('2017-07-01 12:00:00 ', 'yyyy-MM-dd HH24: MI: ss') and to_date ('2017 12:00:00', 'yyyymmdd HH24: MI: SS '); ------------------------------------------------------------------------- SELECT t. *, t. rowid from testdatetime t; -- field: date type (date) www.2cto.com SELECT * FROM testdatetime t WHERE 1 = 1 AND t. startdate = to_date ('2017-07-01 ', 'yyyy-MM-dd'); SELECT * FROM testdatetime t WHERE 1 = 1 AND t. startdate> to_date ('2017-07-01 ', 'yyyy-MM-dd'); SELECT * FROM testdatetime t WHERE 1 = 1 AND t. startdate <to_date ('2017-07-01 ', 'yyyy-MM-dd'); SELECT * FROM testdatetime t WHERE 1 = 1 AND t. startdate> = to_date ('2017-07-01 ', 'yyyy-MM-dd ')
AND t. startdate <= to_date ('1970-07-02 ', 'yyyy-MM-dd'); SELECT * FROM testdatetime t WHERE 1 = 1 AND t. startdate between to_date ('2017-07-01 ', 'yyyy-MM-dd') AND to_date ('2017-07-02', 'yyyy-MM-dd '); ---------------------- SELECT * FROM testdatetime t WHERE 1 = 1 AND t. startdate = to_date ('2017-07-01 12:00:00 ', 'yyyy-MM-dd HH24: MI: ss'); SELECT * FROM testdatetime t WHERE 1 = 1 AND t. startdate> to_date ('2017-07-01 12:00:00 ', 'yyyy-MM-dd HH24: MI: ss'); SELECT * FROM testdatetime t WHERE 1 = 1 AND t. startdate <to_date ('2017-07-01 12:00:00 ', 'yyyy-MM-dd HH24: MI: ss'); SELECT * FROM testdatetime t WHERE 1 = 1 AND t. startdate> = to_date ('2017-07-01 01:00:00 ', 'yyyy-MM-dd HH24: MI: ss') AND t. startdate <= to_date ('2017-07-01 23:00:00 ', 'yyyy-MM-dd HH24: MI: ss '); www.2cto.com SELECT * FROM testdatetime t WHERE 1 = 1 AND t. startdate between to_date ('2017-07-01 01:00:00 ', 'yyyy-MM-dd HH24: MI: ss') AND to_date ('2017-07-01 23:00:00 ', 'yyyy-MM-dd HH24: MI: ss'); --------------------- SELECT * FROM testdatetime t WHERE 1 = 1 AND to_char (t. startdate, 'yyyy-MM-dd') = '2017-07-01 '; SELECT * FROM testdatetime t WHERE 1 = 1 AND to_char (t. startdate, 'yyyy-MM-dd')> '2017-07-01 '; SELECT * FROM testdatetime t WHERE 1 = 1 AND to_char (t. startdate, 'yyyy-MM-dd') <'2017-07-01 '; www.2cto.com SELECT * FROM testdatetime t WHERE 1 = 1 AND to_char (t. startdate, 'yyyy-MM-dd')> = '2017-07-01 'AND to_char (t. startdate, 'yyyy-MM-dd') <= '1970-07-02 '; SELECT * FROM testdatetime t WHERE 1 = 1 AND to_char (t. startdate, 'yyyy-MM-dd') between '2017-07-01 'AND '2017-07-02'; Author: weishaoxiang