OracleSQL Time query

1. When using Oracle's to_date function for date conversion, many Java programmers may use ldquo, yyyy-MM-ddHH: mm: ssrdquo, just like me.

1. When using Oracle's to_date function for date conversion, many Java programmers may use ldquo, yyyy-MM-dd HH: mm: ssrdquo, just like me.

To_date () and 24-hour notation and display of mm minutes:

1. When using Oracle's to_date function for date conversion, many Java programmers may use "yyyy-MM-dd HH: mm: ss format is converted as the format, but an error occurs in Oracle: "ORA 01810 format code appears twice ".
For example, select to_date ('1970-01-01 13:14:20 ', 'yyyy-MM-dd HH24: mm: ss') from dual;
The reason is that SQL statements are case-insensitive. MM and mm are considered to be the same format code, so Oracle SQL uses mi instead of minutes.
Select to_date ('1970-01-01 13:14:20 ', 'yyyy-MM-dd HH24: mi: ss') from dual;

2. Another 24-hour format is used to display HH24.
Select to_char (sysdate, 'yyyy-MM-dd HH24: mi: ss') from dual; // mi is minute
Select to_char (sysdate, 'yyyy-MM-dd HH24: mm: ss') from dual; // mm displays the month

To_date () and to_char () date and String Conversion

To_date ("string to be converted", "format to be converted") The format of the two parameters must match. Otherwise, an error is returned.

That is, the first parameter is interpreted according to the format of the second parameter.

To_char (date, "conversion format") converts a given date according to the "conversion format.

Conversion format:

Y indicates the last yy Of The year. yyy indicates the last two digits of the year. yyyy indicates the last three digits of the year. yyyy indicates the year in four digits.

For month: mm uses two digits to represent the month. mon uses the abbreviation such as January 1, November or nov. month uses the full name such as January 1, November or November.

Dd indicates the day of the month; ddd indicates the day of the year; dy indicates the day of the week, such as Friday or fri; day indicates the day of the week.

For example, Friday or Friday.

Hh 2-digit represents the hour 12-digit; hh24 2-digit represents the hour 24-hour

Indicates minute. mi 2-digit indicates minute.

Indicating second: two-digit ss indicates 60 seconds

For a quarter: q indicates a single digit for a quarter (1-4)

In addition, ww is used to indicate the week w of the current year to indicate the week w of the current month.

Time range in the 24-hour system: 00: 00: 00-23:59:59

Time range in the 12-hour format: 1: 00: 00-12:59:59

For example:

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

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

However, if the preceding writing is performed: select to_date ('2017-12-25, 13: 25: 59', 'yyyy-mm-dd, hh: mi: ss') from dual, an error is reported, because the hour hh is in 12 hexadecimal format and the value 13 is invalid and cannot match.

Supplement:

Current Time minus 7 minutes
Select sysdate, sysdate-interval '7' MINUTE from dual
Current Time minus 7 hours
Select sysdate-interval '7' hour from dual
Current Time minus 7 days
Select sysdate-interval '7' day from dual
Current Time minus July
Select sysdate, sysdate-interval '7' month from dual
Current Time minus 7 years
Select sysdate, sysdate-interval '7' year from dual
Time Interval multiplied by a number
Select sysdate, sysdate-8 * interval '7' hour from dual

The time field is of the DATETIME type.
Select * from table_name where jxlx = '000000' and time between to_date ('1996-9-1 ', 'yyyy-mm-dd ') and to_date ('2014-9-20 23:59:59 ', 'yyyy-mm-dd hh24: mi: ss)

There is also a to_char ()

Comparison with null values: is not null, is null

For example, select * from orders where price is null and price is null.

For example, select * from orders where price is not null, and price is not equal to null