All about SQL PL/SQL and LINUX: Oracle FUNCTIONS with examples 3

DATE FORMAT MODELS:

********************

AD OR A.D./BC OR B.C.INDICATOR:

*********************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'AD')FROM DUAL;

1/16/2012 10:38:15 PM AD

SELECT TO_CHAR(SYSDATE,'B.C.')FROM DUAL;

A.D.

SELECT TO_CHAR(SYSDATE,'A.D.')FROM DUAL;

A.D.

MERIDIAN INDICATOR:

*******************

SELECT SYSDATE,TO_CHAR(SYSDATE,'A.M.') FROM DUAL;

1/17/2012 12:59:11 PM PM

SELECT SYSDATE,TO_CHAR(SYSDATE,'P.M.') FROM DUAL;

1/17/.2012 1:00:22 P.M. P.M.

SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'A.M.') FROM EMP;

ENAME SAL HIREDATE TO_CHAR(HIREDATE,'A.M.')

SMITH 800 12/17/1980 A.M.

ALLEN 1600 2/20/1981 A.M.

WARD 1250 2/22/1981 A.M.

JONES 2975 4/2/1981 A.M.

MARTIN 1250 9/28/1981 A.M.

BLAKE 2850 5/1/1981 A.M.

CLARK 2450 6/9/1981 A.M.

SCOTT 3000 12/9/1982 A.M.

KING 5000 11/17/1981 A.M.

TURNER 1500 9/8/1981 A.M.

ADAMS 1100 1/12/1983 A.M.

JAMES 950 12/3/1981 A.M.

FORD 3000 12/3/1981 A.M.

MILLER 1300 1/23/1982 A.M.

CENTURY INDICATOR:CC

**********************

SELECT SYSDATE,TO_CHAR(SYSDATE,'CC-AD') FROM DUAL;

1/17/2012 1:04:03 PM 21-AD

NUMERIC WEEK DAY INDICATOR:D->(1-7)

************************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'D')FROM DUAL;

1/17/2012 1:05:59 PM 3

SELECT ENAME,SAL,HIREDATE,TO_CHAR(HIREDATE,'D')FROM EMP;

ENAME SAL HIREDATE TO_CHAR(HIREDATE,'D')

SMITH 800 12/17/1980 4

ALLEN 1600 2/20/1981 6

WARD 1250 2/22/1981 1

JONES 2975 4/2/1981 5

MARTIN 1250 9/28/1981 2

BLAKE 2850 5/1/1981 6

CLARK 2450 6/9/1981 3

SCOTT 3000 12/9/1982 5

KING 5000 11/17/1981 3

TURNER 1500 9/8/1981 3

ADAMS 1100 1/12/1983 4

JAMES 950 12/3/1981 5

FORD 3000 12/3/1981 5

MILLER 1300 1/23/1982 7

WEEK DAY SPELLING INDICATOR:->DAY

************************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'DAY')FROM EMP;

SYSDATE TO_CHAR(SYSDATE,'DAY')

1/17/2012 1:08:57 PM TUESDAY

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DAY')FROM EMP WHERE TO_CHAR(HIREDATE,'DY')='MON';

MARTIN 9/28/1981 MONDAY

MONTH DAY INDICATOR:DD

***********************

IT INDICATES THE DAY OF THE MONTH(1-31)

SELECT SYSDATE,TO_CHAR(SYSDATE,'DD-DAY')FROM DUAL;

1/17/2012 2:53:36 PM 17-TUESDAY

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DD-DAY') FROM EMP;

ENAME HIREDATE TO_CHAR(HIREDATE,'DD-DAY')

SMITH 12/17/1980 17-WEDNESDAY

ALLEN 2/20/1981 20-FRIDAY

WARD 2/22/1981 22-SUNDAY

JONES 4/2/1981 02-THURSDAY

MARTIN 9/28/1981 28-MONDAY

BLAKE 5/1/1981 01-FRIDAY

CLARK 6/9/1981 09-TUESDAY

SCOTT 12/9/1982 09-THURSDAY

KING 11/17/1981 17-TUESDAY

TURNER 9/8/1981 08-TUESDAY

ADAMS 1/12/1983 12-WEDNESDAY

JAMES 12/3/1981 03-THURSDAY

FORD 12/3/1981 03-THURSDAY

MILLER 1/23/1982 23-SATURDAY

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DY')FROM EMP WHERE TO_CHAR(HIREDATE,'DD-DY')='28-MON';

MARTIN 9/28/1981 MON

YEAR DAY INDICATOR:DDD

************************

IT INDICATES THE DAY OF THE YEAR(1-366)

SELECT SYSDATE,TO_CHAR(SYSDATE,'DDD') FROM DUAL;

1/17/2012 2:59:06 PM 017

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DDD') FROM EMP;

ENAME HIREDATE TO_CHAR(HIREDATE,'DDD')

SMITH 12/17/1980 352

ALLEN 2/20/1981 051

WARD 2/22/1981 053

JONES 4/2/1981 092

MARTIN 9/28/1981 271

BLAKE 5/1/1981 121

CLARK 6/9/1981 160

SCOTT 12/9/1982 343

KING 11/17/1981 321

TURNER 9/8/1981 251

ADAMS 1/12/1983 012

JAMES 12/3/1981 337

FORD 12/3/1981 337

MILLER 1/23/1982 023

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DDD')FROM EMP WHERE TO_CHAR(HIREDATE,'DY')='MON';

MARTIN 9/28/1981 271

ABBREVIATED WEEK DAY:DY

*************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'D-DY-DAY') FROM DUAL;

1/17/2012 3:05:08 PM 3-TUE-TUESDAY

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'D-DY-DAY')FROM EMP WHERE DEPTNO IN(10,20);

ENAME HIREDATE TO_CHAR(HIREDATE,'D-DY-DAY')

SMITH 12/17/1980 4-WED-WEDNESDAY

JONES 4/2/1981 5-THU-THURSDAY

CLARK 6/9/1981 3-TUE-TUESDAY

SCOTT 12/9/1982 5-THU-THURSDAY

KING 11/17/1981 3-TUE-TUESDAY

ADAMS 1/12/1983 4-WED-WEDNESDAY

FORD 12/3/1981 5-THU-THURSDAY

MILLER 1/23/1982 7-SAT-SATURDAY

ISO STANDARD YEAR WEEK INDICATOR:IW

**************************************

SPECIFIES THE WEEK OF THE YEAR(1-52 OR 1-53)BASED ON THE ISO STANDARD.

SELECT SYSDATE,TO_CHAR(SYSDATE,'IW') FROM DUAL;

1/17/2012 3:10:52 PM 03

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'IW')FROM EMP;

ENAME HIREDATE TO_CHAR(HIREDATE,'IW')

SMITH 12/17/1980 51

ALLEN 2/20/1981 08

WARD 2/22/1981 08

JONES 4/2/1981 14

MARTIN 9/28/1981 40

BLAKE 5/1/1981 18

CLARK 6/9/1981 24

SCOTT 12/9/1982 49

KING 11/17/1981 47

TURNER 9/8/1981 37

ADAMS 1/12/1983 02

JAMES 12/3/1981 49

FORD 12/3/1981 49

MILLER 1/23/1982 03

ISO STANDARD 4 DIGITS YEAR INDICATOR:IYYY

******************************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'IYYY')FROM DUAL;

1/17/2012 3:14:06 PM 2012

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DY')FROM EMP WHERE TO_CHAR(HIREDATE,'DY')='MON';

MARTIN 9/28/1981 MON

FOUR DIGIT YEAR INDICATOR:YYYY OR SYYY

**************************************

SELECT TO_CHAR(SYSDATE,'SYYYY')FROM DUAL;

2012

SELECT HIREDATE,TO_CHAR(HIREDATE,'YYYY'),TO_CHAR(HIREDATE,'YY')FROM EMP;

HIREDATE TO_CHAR(HIREDATE,'YYYY') TO_CHAR(HIREDATE,'YY')

12/17/1980 1980 80

2/20/1981 1981 81

2/22/1981 1981 81

4/2/1981 1981 81

9/28/1981 1981 81

5/1/1981 1981 81

6/9/1981 1981 81

12/9/1982 1982 82

11/17/1981 1981 81

9/8/1981 1981 81

1/12/1983 1983 83

12/3/1981 1981 81

1/23/1982 1982 82

SPELLED YEAR INDICATOR:YEAR OR SYEAR

*************************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'YEAR')FROM DUAL;

1/17/2012 3:21:32 PM TWENTY TWELVE

SELECT SYSDATE,TO_CHAR(SYSDATE,'year')FROM DUAL;

1/17/2012 3:22:51 PM twenty twelve

WEEK OF THE MONTH INDICATOR:W

*******************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'W')FROM DUAL;

1/17/2012 3:28:36 PM 3

SELECT EMPNO,ENAME,HIREDATE,TO_CHAR(HIREDATE,'W') FROM EMP;

EMPNO ENAME HIREDATE TO_CHAR(HIREDATE,'W')

7369 SMITH 12/17/1980 3

7499 ALLEN 2/20/1981 3

7521 WARD 2/22/1981 4

7566 JONES 4/2/1981 1

7654 MARTIN 9/28/1981 4

7698 BLAKE 5/1/1981 1

7782 CLARK 6/9/1981 2

7788 SCOTT 12/9/1982 2

7839 KING 11/17/1981 3

7844 TURNER 9/8/1981 2

7876 ADAMS 1/12/1983 2

7900 JAMES 12/3/1981 1

7902 FORD 12/3/1981 1

7934 MILLER 1/23/1982 4

YEAR WEEK INDICATOR:WW

***********************

SELECT SYSDATE,TO_CHAR(SYSDATE,'WW') FROM DUAL;

1/17/2012 3:31:11 PM 03

SELECT ENAME,EMPNO,HIREDATE,TO_CHAR(HIREDATE,'WW')FROM EMP;

ENAME EMPNO HIREDATE TO_CHAR(HIREDATE,'WW')

SMITH 7369 12/17/1980 51

ALLEN 7499 2/20/1981 08

WARD 7521 2/22/1981 08

JONES 7566 4/2/1981 14

MARTIN 7654 9/28/1981 39

BLAKE 7698 5/1/1981 18

CLARK 7782 6/9/1981 23

SCOTT 7788 12/9/1982 49

KING 7839 11/17/1981 46

TURNER 7844 9/8/1981 36

ADAMS 7876 1/12/1983 02

JAMES 7900 12/3/1981 49

FORD 7902 12/3/1981 49

MILLER 7934 1/23/1982 04

QUARTER OF THE YEAR INDICATOR:Q

**********************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'Q') FROM DUAL;

1/17/2012 3:33:28 PM 1

SELECT EMPNO, ENAME,HIREDATE,TO_CHAR(HIREDATE,'Q')FROM EMP WHERE TO_CHAR(HIREDATE,'Q')=3;

EMPNO ENAME HIREDATE TO_CHAR(HIREDATE,'Q')

7654 MARTIN 9/28/1981 3

7844 TURNER 9/8/1981 3

NUMBER MONTH INDICATOR:MM

*************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'MM-YYYY')FROM DUAL;

1/17/2012 3:42:20 01-2012

SELECT ENAME,TO_CHAR(HIREDATE,'DD-MM-YYYY')FROM EMP WHERE TO_CHAR(HIREDATE,'MM')=12;

ENAME TO_CHAR(HIREDATE,'DD-MM-YYYY')

SMITH 17-12-1980

SCOTT 09-12-1982

JAMES 03-12-1981

FORD 03-12-1981

ABBREVIATED MONTH INDICATOR:MON

********************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'MON') FROM DUAL;

1/17/2012 3:46:03 JAN

SELECT HIREDATE,TO_CHAR(SYSDATE,'MONTH') FROM EMP;

HIREDATE TO_CHAR(SYSDATE,'MONTH')

12/17/1980 JANUARY

2/20/1981 JANUARY

2/22/1981 JANUARY

4/2/1981 JANUARY

9/28/1981 JANUARY

5/1/1981 JANUARY

6/9/1981 JANUARY

12/9/1982 JANUARY

11/17/1981 JANUARY

9/8/1981 JANUARY

1/12/1983 JANUARY

12/3/1981 JANUARY

1/23/1982 JANUARY

TWELVE HOUR CLOCK MODE:HH OR HH12:

***********************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'HH'),TO_CHAR(SYSDATE,'HH12,PM') FROM DUAL;

1/17/2012 3:50:07 PM 03 03,PM

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'HH12:PM')FROM EMP;

ENAME HIREDATE TO_CHAR(HIREDATE,'HH12:PM')

SMITH 12/17/1980 12:AM

ALLEN 2/20/1981 12:AM

WARD 2/22/1981 12:AM

JONES 4/2/1981 12:AM

MARTIN 9/28/1981 12:AM

BLAKE 5/1/1981 12:AM

CLARK 6/9/1981 12:AM

SCOTT 12/9/1982 12:AM

KING 11/17/1981 12:AM

TURNER 9/8/1981 12:AM

ADAMS 1/12/1983 12:AM

JAMES 12/3/1981 12:AM

FORD 12/3/1981 12:AM

MILLER 1/23/1982 12:AM

TWENTY HOUR CLOCK MODE:HH24

****************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'HH24')FROM DUAL;

1/17/2012 3:55:32 PM 15

MINUTES INDICATOR:MI

********************

SELECT SYSDATE,TO_CHAR(SYSDATE,'MM')FROM DUAL;

1/17/2012 3:57:00 PM 01

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'HH:MM')FROM EMP WHERE DEPTNO=20;

ENAME HIREDATE TO_CHAR(HIREDATE,'HH:MM')

SMITH 12/17/1980 12:12

JONES 4/2/1981 12:04

SCOTT 12/9/1982 12:12

ADAMS 1/12/1983 12:01

FORD 12/3/1981 12:12

ROMAN MONTH INDICATOR:RM

**************************

SELECT SYSDATE,TO_CHAR(SYSDATE,'RM'),TO_CHAR(SYSDATE,'DD-RM-YY')FROM DUAL;

1/17/2012 4:02:26 PM I 17-I -12

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DD-RM-YY')FROM EMP WHERE DEPTNO=20;

ENAME HIREDATE TO_CHAR(HIREDATE,'DD-RM-YY')

SMITH 12/17/1980 17-XII -80

JONES 4/2/1981 02-IV -81

SCOTT 12/9/1982 09-XII -82

ADAMS 1/12/1983 12-I -83

FORD 12/3/1981 03-XII -81

SECONDS INDICATOR:SS

*********************

SELECT SYSDATE,TO_CHAR(SYSDATE,'SS'),TO_CHAR(SYSDATE,'HH:MI-SS')FROM DUAL;

1/17/2012 4:06:24 PM 245 04:06-24

SELECT SYSDATE,TO_CHAR(SYSDATE,'SS'),TO_CHAR(SYSDATE,'HH:MI-SS') FROM DUAL;

1/17/2012 4:14:00 PM 00 04:14-00

DATE FORMAT ELEMENT SUFFICES:TH OR SP

**************************************

TH->SUFFIXES THE ORDINAL NUMBER WITH 'st' or 'nd' or 'rd' or 'th'.

SELECT SYSDATE,TO_CHAR(SYSDATE,'DDth,MONTH,YYYY')FROM DUAL;

1/17/2012 4:19:26 PM 17TH,JANUARY ,2012

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DDth,MONTH,YYYY')FROM EMP;

ENAME HIREDATE TO_CHAR(HIREDATE,'DDTH,MONTH,YYYY')

SMITH 12/17/1980 17TH,DECEMBER ,1980

ALLEN 2/20/1981 20TH,FEBRUARY ,1981

WARD 2/22/1981 22ND,FEBRUARY ,1981

JONES 4/2/1981 02ND,APRIL ,1981

MARTIN 9/28/1981 28TH,SEPTEMBER,1981

BLAKE 5/1/1981 01ST,MAY ,1981

CLARK 6/9/1981 09TH,JUNE ,1981

SCOTT 12/9/1982 09TH,DECEMBER ,1982

KING 11/17/1981 17TH,NOVEMBER ,1981

TURNER 9/8/1981 08TH,SEPTEMBER,1981

ADAMS 1/12/1983 12TH,JANUARY ,1983

JAMES 12/3/1981 03RD,DECEMBER ,1981

FORD 12/3/1981 03RD,DECEMBER ,1981

MILLER 1/23/1982 23RD,JANUARY ,1982

SP->SPELLS ORDINAL NUMBER.

SELECT SYSDATE,TO_CHAR(SYSDATE,'DDsp,MONTH,YYYY')FROM DUAL;

1/17/2012 4:29:42 PM SEVENTEEN,JANUARY,2012

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DDsp,MONTH,YYYY')FROM EMP;

ENAME HIREDATE TO_CHAR(HIREDATE,'DDSP,MONTH,YYYY')

SMITH 12/17/1980 SEVENTEEN,DECEMBER ,1980

ALLEN 2/20/1981 TWENTY,FEBRUARY ,1981

WARD 2/22/1981 TWENTY-TWO,FEBRUARY ,1981

JONES 4/2/1981 TWO,APRIL ,1981

MARTIN 9/28/1981 TWENTY-EIGHT,SEPTEMBER,1981

BLAKE 5/1/1981 ONE,MAY ,1981

CLARK 6/9/1981 NINE,JUNE ,1981

SCOTT 12/9/1982 NINE,DECEMBER ,1982

KING 11/17/1981 SEVENTEEN,NOVEMBER ,1981

TURNER 9/8/1981 EIGHT,SEPTEMBER,1981

ADAMS 1/12/1983 TWELVE,JANUARY ,1983

JAMES 12/3/1981 THREE,DECEMBER ,1981

FORD 12/3/1981 THREE,DECEMBER ,1981

MILLER 1/23/1982 TWENTY-THREE,JANUARY ,1982

SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DDspth,MONTH,YYYY')FROM EMP;

ENAME HIREDATE TO_CHAR(HIREDATE,'DDSPTH,MONTH,YYYY')

SMITH 12/17/1980 SEVENTEENTH,DECEMBER ,1980

ALLEN 2/20/1981 TWENTIETH,FEBRUARY ,1981

WARD 2/22/1981 TWENTY-SECOND,FEBRUARY ,1981

JONES 4/2/1981 SECOND,APRIL ,1981

MARTIN 9/28/1981 TWENTY-EIGHTH,SEPTEMBER,1981

BLAKE 5/1/1981 FIRST,MAY ,1981

CLARK 6/9/1981 NINTH,JUNE ,1981

SCOTT 12/9/1982 NINTH,DECEMBER ,1982

KING 11/17/1981 SEVENTEENTH,NOVEMBER ,1981

TURNER 9/8/1981 EIGHTH,SEPTEMBER,1981

ADAMS 1/12/1983 TWELFTH,JANUARY ,1983

JAMES 12/3/1981 THIRD,DECEMBER ,1981

FORD 12/3/1981 THIRD,DECEMBER ,1981

MILLER 1/23/1982 TWENTY-THIRD,JANUARY ,1982

SELECT SYSDATE,TO_CHAR(SYSDATE,'DDspth,Month YYYYSP')FROM DUAL;

1/17/2012 5:32:46 PM SEVENTEENTH,january TWO THOUSAND TWELVE

FILL MODE INDICATOR:FM

***********************

SELECT SYSDATE,TO_CHAR(SYSDATE,'DDSPTH MONTH YYYYSP'),TO_CHAR(SYSDATE,'FMDDSPTH MONTH YYYYSP') FROM DUAL;

1/17/2012 5:35:50 PM SEVENTEEN JANUARY TWO THOUSAND TWELVE SEVENTEENTH JANUARY TWO THOUSAND TWELVE

TO_NUMBER FUN:

***************

SELECT '$10,000.00',TO_NUMBER('$10,000.00','L99,999.99')FROM DUAL;

$10,000.00 10000

SELECT '$10,000.00',TO_NUMBER('$10,000.00','L99,999.99')+500 FROM DUAL;

$10,000.00 10500

TO_DATE FUN:

************

SYN:TO_DATE(CHAR,'FMT')

SELECT TO_CHAR(TO_DATE('12-JAN-1980'),'DDSP')FROM DUAL;

TWELVE

SELECT ENAME,HIREDATE,ADD_MONTHS(TO_DATE('1980-DECEMBER-17','YYYY-MONTH-DD'),3)FROM EMP WHERE HIREDATE='17-DEC-1980';

SMITH 12/17/1980 3/17/1981

SELECT ENAME,HIREDATE,ADD_MONTHS(TO_DATE('1980-DECEMBER-17','YYYY-MONTH-DD'),3)FROM EMP WHERE TO_CHAR(HIREDATE,'FMYYYY-MONTH-DD')='1980-DECEMBER-17';

SMITH 12/17/1980 3/17/1981

INSER STATEMENT WITH INVALID NUMBER:

************************************

CREATE TABLE TEMP(EMPNO NUMBER(8),

DOJ DATE);

INSERT INTO TEMP VALUES(TO_NUMBER('1,23,456','9G99G999'),SYSDATE);

INSERT INTO TEMP VALUES(TO_NUMBER('1,23,456-','9G99G999MI'),SYSDATE);

SELECT * FROM TEMP;

EMPNO DOJ

123456 1/17/2012 5:54:22 PM

-123456 1/17/2012 5:55:37 PM

INVALID DATES:

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

INSERT INTO TEMP VALUES(1001,'12-JAN-09 11:30:15');

INSERT INTO TEMP VALUES(1001,TO_DATE('12-JAN-2009 11:30:15 P.M.','DD-MON-YYYY HH:MI:SS P.M');

INSERT INTO TEMP VALUES(1001,TO_DATE('12-JAN-3712 B.C.','DD-MON-YYYY B.C.'));

INSERT INTO TEMP VALUES(1001,TO_DATE(100,'J'));

SELECT TO_DATE(10,'DD')FROM DUAL;

1/10/2012

SELECT TO_DATE(10,'DDD')FROM DUAL;

1/10/2012

SELECT TO_DATE(1,'W')FROM DUAL; --IT IS INVALID CONVERSION

SELECT TO_DATE(1,'WW')FROM DUAL; --IT IS INVALID CONVERSION

All about SQL PL/SQL and LINUX

Tuesday, 7 August 2012

Oracle FUNCTIONS with examples 3

No comments:

Post a Comment

Expose like a sun.