SYSDATE ADD_MONTHS

---------- ----------

2004-07-24 2004-09-24

 

SQL>

SQL> -- LAST_DAY(дата) - последний день месяца

SQL> SELECT SYSDATE,

2 LAST_DAY(SYSDATE) "Last",

3 (LAST_DAY(SYSDATE) - SYSDATE) "Days Left"

4 FROM DUAL;

 

SYSDATE Last Days Left

---------- ---------- ----------

2004-07-24 2004-07-31 7

 

SQL>

SQL> -- MONTHS_BETWEEN(начальная_дата, конечная_дата) - число месяцев между датами

SQL> SELECT MONTHS_BETWEEN(SYSDATE, date_value) FROM test_3;

 

MONTHS_BETWEEN(SYSDATE,DATE_VALUE)

----------------------------------

6.41304958

-2.3288859

-5.1998536

 

SQL>

SQL> -- EXTRACT - извлечь часть даты

SQL> SELECT EXTRACT(YEAR FROM date_value) FROM test_3;

 

EXTRACT(YEARFROMDATE_VALUE)

---------------------------

 

SQL> SELECT EXTRACT(MONTH FROM date_value) FROM test_3;

 

EXTRACT(MONTHFROMDATE_VALUE)

----------------------------

 

SQL> SELECT EXTRACT(DAY FROM date_value) FROM test_3;

 

EXTRACT(DAYFROMDATE_VALUE)

--------------------------

 

SQL>

SQL> --

SQL> -- Функции преобразования данных

SQL> --

SQL>

SQL> -- TO_CHAR - преобразовать выражение к строке

SQL> SELECT TO_CHAR(100) FROM DUAL;

 

TO_

---

 

SQL> SELECT CONCAT('Number = ', TO_CHAR(100)) FROM DUAL;

 

CONCAT('NUMB

------------

Number = 100

 

SQL> SELECT CONCAT('LOG(10, 232) = ', TO_CHAR(LOG(10, 232))) FROM DUAL;

 

CONCAT('LOG(10,232)=',TO_CHAR(LOG(10,232)))

-------------------------------------------------------

LOG(10, 232) = 2.36548798489089967297406344714273407156

 

SQL> -- TO_DATE - преобразовать выражение к типу дата

SQL> SELECT TO_DATE('2001/04/04') FROM DUAL;

 

TO_DATE(`2

----------

2001-04-04

 

SQL> SELECT TO_DATE('21-04-2004', 'DD-MM-YYYY') FROM DUAL;

 

TO_DATE(`2

----------

2004-04-21

 

SQL> SELECT TO_DATE('2004.12.01', 'YYYY.MM.DD') FROM DUAL;

 

TO_DATE("2

----------

2004-12-01

 

SQL> SELECT ADD_MONTHS(TO_DATE('2001/04/04'), 2) FROM DUAL;

 

ADD_MONTHS

----------

2001-06-04

 

SQL> SELECT LAST_DAY(TO_DATE('2004/04/04')) FROM DUAL;

 

LAST_DAY(T

----------

2004-04-30

 

SQL>

SQL> -- TO_NUMBER - преобразовать выражение к типу число

SQL> SELECT TO_NUMBER('500') FROM DUAL;

 

TO_NUMBER('500')

----------------

 

SQL> SELECT SQRT(TO_NUMBER('500') + 11) FROM DUAL;

 

SQRT(TO_NUMBER('500')+11)

-------------------------

22.6053091

 

SQL> SELECT TO_NUMBER(SUBSTR('Value=500', -3, 3)) FROM DUAL;

 

TO_NUMBER(SUBSTR('VALUE=500',-3,3))

-----------------------------------

 

SQL>

SQL> -- CAST - преобразование типов выражений

SQL> SELECT CAST('2004-10-20' AS DATE) FROM DUAL;

 

CAST('2004

----------

2004-10-20

 

SQL> SELECT CAST(15 AS VARCHAR2(10)) FROM DUAL;

 

CAST(15ASV

----------

 

SQL> SELECT CAST('2004' AS INT) FROM DUAL;

 

CAST('2004'ASINT)

-----------------

 

SQL> SELECT CAST(EXP(8.2) AS VARCHAR2(50)) FROM DUAL;

 

CAST(EXP(8.2)ASVARCHAR2(50))

--------------------------------------------------

3640.95030733235472156857183395742043191

 

SQL> --

SQL> -- Специальные функции и выражения

SQL> --

SQL>

SQL> -- CASE - проверка набора условий

SQL> SELECT CASE WHEN number_value > 1000 THEN 'BIG number'

2 ELSE 'small number'

3 END

4 FROM test_3;

 

CASEWHENNUMB

------------

BIG number

BIG number

small number

small number

 

SQL>

SQL> SELECT CASE WHEN number_value < 1000 THEN 'small number'

2 WHEN number_value < 2000 THEN 'medium number'

3 ELSE 'BIG number'

4 END

5 FROM test_3;

 

CASEWHENNUMBE

-------------

medium number

BIG number

small number

small number

 

SQL>

SQL> -- DECODE - сравнение выражения с набором значений

SQL> SELECT DECODE(10, 1, 'Expression = 1',

2 2, 'Expression = 2',

3 10, 'Expression = 10',

4 'Not found')

5 FROM DUAL;

 

DECODE(10,1,EX

---------------

Expression = 10

 

SQL>

SQL> SELECT DECODE(3, 1, 'Expression = 1',

2 2, 'Expression = 2',

3 10, 'Expression = 10',

4 'Not found')

5 FROM DUAL;

 

DECODE(3,

---------