TO CHAR Function with Dates

TO_CHAR Function with Dates

TO_CHAR(date, 'fmt')

The format model: ? Must be enclosed in single quotation marks and is case sensitive ? Can include any valid date format element ? Has an fm element to remove padded blanks or suppress

leading zeros ? Is separated from the date value by a comma

Displaying a Date in a Specific Format Previously, all Oracle date values were displayed in the DD-MON-YY format. The TO_CHAR function allows you to convert a date from this default fomat to one specified by you. Guidelines

? The format model must be enclosed in single quotation marks and is case sensitive;

? The format model can include any valid date format element. Be sure to separate the date value from the format model by a comma.

? The names of days and months in the output are automatically padded with blanks.

? To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

? Yon can resize the display width of the resulting character field with the SQL*Plus COLUMN command.

? The resultant column width is 80 characters by default.

1

Examples

SELECT empno, TO_CHAR(hiredate, 'MM/YY') "E GR TARH" FROM emp WHERE ename = 'BLAKE' ;

EMP O 7698

E AME BLAKE

E GR 05/81

SELECT ename, empno TO_CHAR( hiredate , 'fmMonth DD, YYYY' ) Hiredate FROM emp WHERE deptno = 30

E AME ALLEN WARD MARTIN BLAKE TURNER JAMES

6 rows selected.

EMP O

HIREDATE

7499 ubat 20, 1981

7521 ubat 22, 1981

7654 Eyl?l 28, 1981

7698 Mayis 1, 1981

7844 Eyl?l 8, 1981

7900 Aralik 3, 1981

SELECT ename, hiredate, TO_CHAR( hiredate , 'fmDY.Mon.YYY' ) FROM EMP;

E AME HIREDATE TO_CHAR(HIREDATE,'FMDY.MO .YYY') SMITH 17/12/1980 ?AR.Ara.980 ALLEN 20/02/1981 CUM.ub.981 ... 14 rows selected.

2

Using TO_CHAR Function with Dates

SELECT ename, hiredate, TO_CHAR(hiredate, 'DD Month YYYY' ) "eGiri Tarihi" FROM EMP;

E AME SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

14 rows selected.

HIREDATE 17/12/1980 20/02/1981 22/02/1981 02/04/1981 28/09/1981 01/05/1981 09/06/1981 09/12/1982 17/11/1981 08/09/1981 12/01/1983 03/12/1981 03/12/1981 23/01/1982

e Giri Tarihi 17 Aralik 1980 20 ubat 1981 22 ubat 1981 02 Nisan 1981 28 Eyl?l 1981 01 Mayis 1981 09 Haziran 1981 09 Aralik 1982 17 Kasim 1981 08 Eyl?l 1981 12 Ocak 1983 03 Aralik 1981 03 Aralik 1981 23 Ocak 1982

3

Example

SELECT sysdate, TO_CHAR ( sysdate, 'fmDD.MM.YYYY fmHH:MI:SS PM' ) Zaman FROM dual ;

SYSDATE 18/03/2007

ZAMA 18.3.2007 06:23:02 ?S

SELECT sysdate, TO_CHAR ( sysdate, 'fmDD.MM.YYYY fmHH:MI:SS AM' ) Zaman FROM dual ;

SYSDATE 18/03/2007

ZAMA 18.3.2007 06:23:02 ?S

SELECT sysdate,

TO_CHAR ( sysdate, 'fmDD.MM.YYYY fmHH12:MI:SS PM' ) Zaman

FROM

dual ;

SYSDATE 18/03/2007

ZAMA 18.3.2007 06:23:02 ?S

4

SELECT sysdate, TO_CHAR ( sysdate, 'fmDD.MM.YYYY fmHH24:MI:SS' ) Zaman FROM dual ;

SYSDATE 18/03/2007

ZAMA 18.3.2007 18:23:02

SELECT sysdate, TO_CHAR ( sysdate, 'fmDD.MM.YYYY fmHH24:MI:SS AM' ) Zaman FROM dual ;

SYSDATE 18/03/2007

ZAMA 18.3.2007 18: 23:02 ?S

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download