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

sensit?ve;

? 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 f?eld 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 G?R?? TAR?H?"

FROM emp

WHERE ename = 'BLAKE' ;

EMP O

E AME

??E G?R??

7698

BLAKE

05/81

SELECT ename, empno

TO_CHAR( hiredate , 'fmMonth DD, YYYY' ) Hiredate

FROM emp

WHERE deptno = 30

E AME

EMP O

HIREDATE

ALLEN

7499 ?ubat 20, 1981

WARD

7521 ?ubat 22, 1981

MARTIN

7654 Eyl¨¹l 28, 1981

BLAKE

7698 May?s 1, 1981

TURNER

7844 Eyl¨¹l 8, 1981

JAMES

7900 Aral?k 3, 1981

6 rows selected.

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

HIREDATE

17/12/1980

??e Giri? Tarihi

17 Aral?k 1980

ALLEN

20/02/1981

20 ?ubat 1981

WARD

22/02/1981

22 ?ubat 1981

JONES

02/04/1981

02 Nisan 1981

MARTIN

28/09/1981

28 Eyl¨¹l 1981

BLAKE

01/05/1981

01 May?s 1981

CLARK

09/06/1981

09 Haziran 1981

SCOTT

09/12/1982

09 Aral?k 1982

KING

17/11/1981

17 Kas?m 1981

TURNER

08/09/1981

08 Eyl¨¹l 1981

ADAMS

12/01/1983

12 Ocak 1983

JAMES

03/12/1981

03 Aral?k 1981

FORD

03/12/1981

03 Aral?k 1981

MILLER

23/01/1982

23 Ocak 1982

14 rows selected.

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