Oracle Format String accurate to seconds

To_date ('2017-3-24 ', 'yyyy-MM-DD hh24: MI: ss ')

Source: http://www.hkln.net/doc/oracle/ SQL /function.htm#a8

Introduction

Next Shard to shard

Oracle provides some basic data types for Data calculation. These data types can be classified as follows:

• Text
• Data
• Day
• It is similar.

The function numbers introduced in this chapter are the information used to calculate a row. The function is called single-row function ), in addition, there is a kind of group function used to calculate the data of multiple rows, grouping letter numbers will be introduced in a later chapter.

For more information, see Oracle SQL reference.

Dual table Lattice

Next region in the previous region to zookeeper

In the preceding example, the Select Sub-Statement from sub-statement contains the table name of an item, for example, EMP, however, sometimes we will use information to perform some simple operations, for example, 1 + 2, this root does not need to use any table, so what should the from subsentence be? The answer is dual.

SQL> SELECT 1 + 2 FROM DUAL;       1+2----------         3

Because each select Sub-statement must have a from sub-statement, Oracle will act as a dual table lattice to match this rule.

The dual table can also be used to provide some information, such as the user name and system time.

SQL> SELECT USER FROM DUAL;USER------------------------------SCOTTSQL> SELECT SYSDATE FROM DUAL;SYSDATE---------03-NOV-02

　

　

Data correspondence

Next region in the previous region to zookeeper

Function	Returned information
ABS (X)	Comparison Value
Round (x, y)	If y is positive, the right vertex of the small digit is taken, and the left vertex is taken
Ceil (X)	It is not much different from round (x, 0), but it will round up
Floor (X)	It is not much different from Ceil (x), but it will round down
MoD (x, y)	Divide X by values of Y
Sign (X)	If X is positive, 1 is returned,-1 is returned, and 0 is returned.
SQRT (X)	Pingfanggen
Trunc (x, y)	It is not much different from round (x, y), but it is truncation.

Example:

SQL> SELECT ABS(2), ABS(-2) FROM DUAL    ABS(2)    ABS(-2)---------- ----------         2          2SQL> SELECT ROUND(1.5, 0), ROUND(0.15, 1), ROUND(15, -1) FROM DUAL;ROUND(1.5,0) ROUND(0.15,1) ROUND(15,-1)------------ ------------- ------------           2            .2           20SQL> SELECT CEIL(1.4), CEIL(1.5), CEIL(-1.4), CEIL(-1.5) FROM DUAL; CEIL(1.4)  CEIL(1.5) CEIL(-1.4) CEIL(-1.5)---------- ---------- ---------- ----------         2          2         -1         -1SQL> SELECT FLOOR(1.4), FLOOR(1.5), FLOOR(-1.4), FLOOR(-1.5) FROM DUAL;FLOOR(1.4) FLOOR(1.5) FLOOR(-1.4) FLOOR(-1.5)---------- ---------- ----------- -----------         1          1          -2          -2SQL> SELECT MOD(4, 2), MOD(4,3), MOD(4,5) FROM DUAL;  MOD(4,2)   MOD(4,3)   MOD(4,5)---------- ---------- ----------         0          1          4SQL> SELECT SIGN(2), SIGN(-2), SIGN(0) FROM DUAL;   SIGN(2)   SIGN(-2)    SIGN(0)---------- ---------- ----------         1         -1          0SQL> SELECT SQRT(4), SQRT(2) FROM DUAL;   SQRT(4)    SQRT(2)---------- ----------         2 1.41421356SQL> SELECT TRUNC(12.34, 0), TRUNC(12.34, 1), TRUNC(12.34, -1) FROM DUAL;TRUNC(12.34,0) TRUNC(12.34,1) TRUNC(12.34,-1)-------------- -------------- ---------------            12           12.3              10

　

Text Letter Number

Next region in the previous region to zookeeper

Function	Returned information
Lpad (X, Y [, Z])	Add Z to the left margin of X (the token is an empty grid) to make it grow to y.
Rpad (X, Y [, Z])	In the same way, add the right cursor of X to the Z.
Lower (X)	Converts all the tokens of X into small tokens.
Upper (X)	Converts all the characters in X into a large value.
Initcap (X)	Converts the first dollar of each English word of X into a large dollar, and the dollar of X into a small dollar.
Length (X)	X length
Substr (X, Y [, Z])	Start with the Y-th dollar of X, and draw Z-th yuan (if you want to extract all the characters ).
Instr (x, y)	Y is in the position of X.
Concat (x, y)	Connect X and Y. You can also use the | (two straight lines) operator.

Example:

SQL> SELECT LPAD(ENAME, 10, '=') FROM EMP WHERE ENAME = 'SCOTT'LPAD(ENAME,10,'=')---------------------=====SCOTTSQL> SELECT RPAD(ENAME, 10, '=') FROM EMP WHERE ENAME = 'SCOTT'RPAD(ENAME,10,'=')---------------------SCOTT=====SQL> SELECT LOWER(ENAME) FROM EMP WHERE ENAME = 'SCOTT';LOWER(ENAM----------scottSQL> SELECT INITCAP(ENAME) FROM EMP WHERE ENAME = 'SCOTT';INITCAP(EN----------ScottSQL> SELECT LENGTH(ENAME) FROM EMP WHERE ENAME = 'SCOTT';LENGTH(ENAME)-------------            5SQL> SELECT SUBSTR(ENAME, 2, 3) FROM EMP WHERE ENAME = 'SCOTT';SUBSTR------COTSQL> SELECT INSTR(ENAME, 'COT') FROM EMP WHERE ENAME = 'SCOTT';INSTR(ENAME,'COT')------------------                 2SQL> SELECT ENAME || '_' || JOB || '_' || SAL FROM EMP WHERE ENAME = 'SCOTT';ENAME||'_'||JOB||'_'||SAL-------------------------------------------------------------SCOTT_ANALYST_3000

Description:

SQL> select substr (ename, 2, 3) from EMP where ename = 'Scott ';

Substr

------

Cot

Set the bit of the first metadatabase to 1 instead of 0.

Nvl data

Next region in the previous region to zookeeper

Nvl functions can convert null values into another item. If you want to calculate the total salary of each employee in the EMP table, the total salary is calculated by Sal + comm. You can calculate the total salary:

SQL> SELECT ENAME, SAL, COMM, (SAL + COMM) TOTAL_SALARY FROM EMP;ENAME             SAL       COMM TOTAL_SALARY---------- ---------- ---------- ------------SMITH             800ALLEN            1600        300         1900WARD             1250        500         1750JONES            2975MARTIN           1250       1400         2650BLAKE            2850CLARK            2450SCOTT            3000KING             5000TURNER           1500          0         1500ADAMS            1100JAMES             950FORD             3000MILLER           130014 rows selected.

Why are some rows of total_salary empty? Because their comm is null, and null is added to the data, it will be null.

The solution is to convert the null value to 0, so null cannot be obtained.

SQL> SELECT ENAME, SAL, NVL(COMM, 0), (SAL + NVL(COMM, 0)) TOTAL_SALARY FROM EMP;ENAME             SAL NVL(COMM,0) TOTAL_SALARY---------- ---------- ----------- ------------SMITH             800           0          800ALLEN            1600         300         1900WARD             1250         500         1750JONES            2975           0         2975MARTIN           1250        1400         2650BLAKE            2850           0         2850CLARK            2450           0         2450SCOTT            3000           0         3000KING             5000           0         5000TURNER           1500           0         1500ADAMS            1100           0         1100JAMES             950           0          950FORD             3000           0         3000MILLER           1300           0         130014 rows selected.

Description:

Nvl (Comm, 0)

If comm is null, return 0. Otherwise, return comm.

Decode data

Next region in the previous region to zookeeper

The functions of the decode function can be a bit like the if-then-else sentence in the Cheng Yan statement. It can contain different values of the root token, when different resources are returned, the statement is as follows:

Decode (column_name

, Value1, substitute1

, Value2, substitute2

,...

, Default

)

If the value of column_name is value1, then it returns to substitute1, value2, and then returns to substitute2. If this type is used, if none of the above values are used, it returns to default.

In fact, decode can also be used for nvl performance. For example, nvl (Comm, 0) can be written as follows:

Decode (Comm, null, 0, comm)

If you want to list the department names of each employee, you can use decode in addition to connecting EMP to Dept.

SQL> SELECT ENAME  2      ,DECODE(DEPTNO  3          ,10 ,'Accounting'  4          ,20 ,'Research'  5          ,30 ,'Sales'  6          ,40 ,'Opeartions'  7          ,'UNKNOWN'  8      ) DEPARTMENT  9   FROM EMP 10   ;ENAME      DEPARTMENT---------- -------------SMITH      ResearchALLEN      SalesWARD       SalesJONES      ResearchMARTIN     SalesBLAKE      SalesCLARK      AccountingSCOTT      ResearchKING       AccountingTURNER     SalesADAMS      ResearchJAMES      SalesFORD       ResearchMILLER     Accounting14 rows selected.

　

Number of calendar days

Next region in the previous region to zookeeper

Oracle uses data to store daily data. The full data table of the data is the same as that of Julian Calendar (starting from January 1, January 1, 4712 BC ), the hour, minute, and second of the small data table.

Example:

SQL> SELECT SYSDATE ,SYSDATE - 1 YESTERDAY ,SYSDATE + 1 TOMORROW  2  FROM DUAL;SYSDATE   YESTERDAY TOMORROW--------- --------- ---------03-NOV-02 02-NOV-02 04-NOV-02

Description:

Indicates that the daily task is counted as a data task.

Function	Returned information
Add_months (x, y)	Date of month y of X
Last_day (X)	The last day of the month specified by X
Months_between (x, y)	The time between x and y is less than a month. If X is less than Y, a regression is returned.
Next_day (x, y)	Next star period y of X

Example:

SQL> SELECT ADD_MONTHS('3-NOV-02', 2) FROM DUAL;ADD_MONTH---------03-JAN-03SQL> SELECT LAST_DAY('3-NOV-02') FROM DUAL;LAST_DAY(---------30-NOV-02SQL> SELECT MONTHS_BETWEEN('3-NOV-02', '1-OCT-02') FROM DUAL;MONTHS_BETWEEN('3-NOV-02','1-OCT-02')-------------------------------------                           1.06451613SQL> SELECT NEXT_DAY('2-NOV-02', 'TUESDAY') FROM DUAL;NEXT_DAY(---------05-NOV-02

　

Date correspondence: to_char correspondence

Next region in the previous region to zookeeper

You can use the to_char function to convert the daily data into a specified text format.

Example:

SQL> SELECT SYSDATE FROM DUAL;SYSDATE---------03-NOV-02SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') FROM DUAL;TO_CHAR(SYSDATE,'DD---------------------03-NOV-2002 19:52:49

Description:

The second limit of the to_char function is the limit format:

Format character	Description
D	Star period (1 to 7)
Dd	Day of the month (1 to 31)
Mon	Monthly subscription)
Month	Monthly (all-round)
YY	Year (2 digits)
Yyyy	Year (4 digits)
Rr	Year (2 digits, millennium-compliant ))
Rrrr	Year (four digits, millennium-compliant ))
HH	Time (2 digits)
Hh24	Time (2 digits, 24 hours)
Mi	Minute (2 digits)
SS	Second (2 digits)

　

Date correspondence: to_date correspondence

Last updated to zookeeper

The to_date function is used to convert the text into a date. For example, when you table a date in SQL, you can use the following statement:

SQL> SELECT TO_DATE('03-NOV-02', 'DD-MON-RR') FROM DUAL;TO_DATE('---------03-NOV-02

The first parameter is the text content, and the second parameter is the text content format, oracle converts the text into a day.

If you want to know which of the following operators in the EMP table are affected on or after February 1, January 1, 1987, refer to the following example:

SQL> SELECT ENAME, HIREDATE FROM EMP  2  WHERE TO_DATE('01-JAN-1987', 'DD-MON-YYYY') <= HIREDATE  3  ;ENAME      HIREDATE---------- ---------SCOTT      19-APR-87ADAMS      23-MAY-87

Because hiredate is a day, you must use to_date to convert your SQL text into a day to make a comparison.

If you have not entered the second phase, Oracle will use the date format set by the primary node, it is the number of records stored in a table named nls_date_format. You can obtain the values in the table format v $ nls_parameters:

SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';PARAMETER----------------------------------------------------------------VALUE----------------------------------------------------------------NLS_DATE_FORMATDD-MON-RRSQL> SELECT ENAME, HIREDATE FROM EMP  2  WHERE TO_DATE('01-JAN-1987') <= HIREDATE  3  ;ENAME      HIREDATE---------- ---------SCOTT      19-APR-87ADAMS      23-MAY-87

From this we can see that the lattice set by the operator is a DD-MON-RR, so there is no second limit in to_date and there is no problem.