Oracle Character and Time comparison

fields in the database 2017-07-11 13:37:51 type is char or varchar

To compare the input with ' 20170625 ', the format is inconsistent and needs to be converted to: YYYYMMDD string

1, first to_dateto_date (create_date, ' yyyy-mm-dd,hh24:mi:ss ') format must be consistent with Create_date

2. Turn string To_char (sysdate, ' YYYYMMDD ')

Cases:

The following will create_date into the format you want

Select Create_date CreateDate,

To_char (To_date (create_date, ' yyyy-mm-dd,hh24:mi:ss '), ' YYYYMMDD ') date1,
To_char (To_date (create_date, ' yyyy-mm-dd,hh24:mi:ss '), ' yyyy/mm/dd ') Date2,
To_char (To_date (create_date, ' yyyy-mm-dd,hh24:mi:ss '), ' Yyyymmddhh24miss ') date3
From Pfb_payment_order_info;

Format of conversion:

Represents year's: Y represents the last 2 digits of the year yyy represents the last 3 digits of the year yyyy in 4-digit years

MM with 2 digits for month; Mon in abbreviated form such as November or Nov; month with full name such as November or November

Indicates day: DD indicates days of the month, DDD indicates the day of the year, DY when in for a few days, such as Friday or Fri;day when in a few days to write all

Like Friday or Friday.

Represents hour: HH 2 digits for hour 12, hh24 2 digits for hours 24 hours

Represents minute: Mi 2 digits for minutes

Represents second: SS 2 digits for seconds 60 binary

Indicates quarterly: Q A number represents quarter (1-4)

There is also WW used to indicate the week of the year when W is used to denote the month ordinal.

Time range under 24-hour system: 00:00:00-23:59:59

Time range under 12-hour system: 1:00:00-12:59:59

Like what:

Select To_char (sysdate, ' Yy-mm-dd hh24:mi:ss ') from dual//display: 08-11-07 13:22:42

Select To_date (' 2005-12-25,13:25:59 ', ' Yyyy-mm-dd,hh24:mi:ss ') from dual//display: 2005-12-25 13:25:59

And if the writing: Select To_date (' 2005-12-25,13:25:59 ', ' yyyy-mm-dd,hh:mi:ss ') from the dual, will be an error, because the hour HH is 12 binary, 13 is illegal input, can not match.

Add:

Time minus 7 minutes for the current time
Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual
The current time minus 7 hours of time
Select Sysdate-interval ' 7 ' hour from dual
The current time minus 7 days
Select Sysdate-interval ' 7 ' Day from dual
Time minus July for current time
Select Sysdate,sysdate-interval ' 7 ' month from dual
Time minus 7 years in the current time
Select Sysdate,sysdate-interval ' 7 ' year from dual
Time interval multiplied by a number
Select Sysdate,sysdate-8*interval ' 7 ' hour from dual

First correct a few questions:
(1) SELECT * from TB where createtime > ' 20111109 '
If you want this condition to be satisfied, the TB table createtime can not be written as ' 2011/11/10 ' this format, can only be written as ' 20111110 ', because ' 2011/11/10 ' is not greater than ' 20111109 ', only ' 20111110 ' is greater than ' 20111109 '.
(2) When comparing, it is not automatically converted to date type, is the ACSII code to compare its characters.
So ' 2011/11/10 ' is not greater than ' 20111109 ', because the first four characters are 2011, certainly equal, compared to the 5th character, with '/' and ' 1 ' comparisons, and select 1 from dual where '/' > ' 1 ' is not true, Description '/' Less than ' 1 ', so ' 2011/11/10 ' is less than ' 20111109 '

Oracle Character and Time comparison