Conversion Function (learning notes), conversion function learning notes

Conversion Function (learning notes), conversion function learning notes

-- ****************************** Conversion Function
-- Format the current date and time

Select sysdate, to_char (SYSDATE, 'yyyy-MM-DD hh24: mi: ss') format date, to_char (SYSDATE, 'fmyyyy-MM-DD hh24: mi: ss ') add FM to remove 0 FROM dual;

-- Use another format date

Select sysdate current time, to_char (SYSDATE, 'Year-MONTH-DY '), to_char (SYSDATE, 'Year-MONTH-D'), to_char (SYSDATE, 'Year-MONTH-W ') FROM dual;

-- Query Information about all employees who have been hired in July of every year

SELECT *FROM emp eWHERE to_char(e.hiredate,'MM')='02';

-- Or directly write 2

SELECT *FROM emp eWHERE to_char(e.hiredate,'MM')=2;

 

-- Display the employment date and date of each employee, which must be year-month-day

SELECT e. empno, e. ename, e. job, to_char (e. hiredate, 'yyyy-MM-DD ') Date of entry, to_char (e. hiredate, 'yyyy'), to_char (e. hiredate, 'mm') month, to_char (e. hiredate, 'dd') FROM emp e;

-- Write date connection

SELECT e. empno, e. ename, e. job, to_char (e. hiredate, 'yyyy-MM-DD ') Date of entry, to_char (e. hiredate, 'yyyy') | 'Year' | to_char (e. hiredate, 'mm') | 'month' | to_char (e. hiredate, 'dd') | 'day' employment date FROM emp e;

-- Use the English date format to indicate the start date

SELECT e. empno, e. ename, e. job, to_char (e. hiredate, 'Year-month-dy ') FROM emp e;

-- Format Numeric Display

SELECT to_char (987654321.789, '123') format number 1, to_char (999,999,999,999,999.99999, '123') format Number 2 FROM dual;

-- Add currency display

SELECT to_char (987654321.789, 'l999, 999,999,999,999.99 ') display currency, to_char (987654321.789,' $000,000,000,000,000.00 ') display dollar FROM dual;

-- To_date Function

SELECT to_date('1979-09-19','YYYY-MM-DD')FROM dual;

-- To timestamp Function

SELECT to_timestamp ('2017-09-19 18:07:10 ', 'yyyy-MM-DD hh24: mi: ss') display time, to_timestamp ('2017-09-19 18:07:10 ', 'fmyyyy-MM-DD hh24: mi: ss') the display time removes 0 FROM dual;

-- To number function, basically useless

SELECT to_number ('09') + to_number ('19') Addition, to_number ('09')-to_number ('19') subtraction FROM dual;