TO CHAR Function with Dates
[Pages:24]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
SELECT ename,
TO_CHAR(hiredate, 'fmDdspth "of" Month YYYY fmHH:MI:SS PM' ) HIREDATE
FROM
emp;
E AME SMITH ALLEN
HIREDATE Seventeenth of Aralik 1980 12:00:00 ?? Twentieth of ubat 1981 12:00:00 ??
...
14 rows selected.
6
Examples : YY , CC , SCC
SELECT TO_CHAR( TO_DATE('28-11-1942') , 'YY') Y?zyil FROM dual;
YIL
42
SELECT TO_CHAR( TO_DATE('28-11-1942') , 'CC') Y?zyil FROM dual;
Y?ZYIL 20 SELECT TO_CHAR( TO_DATE('28-11-2942') , 'SCC') Binyil FROM dual;
BI YIL 30
7
Examples : D , DD , DDD
SELECT TO_CHAR( TO_DATE('28-11-1942') , 'fmd' ) "Day Of Week" FROM dual ;
Day Of Week 6 SELECT TO_CHAR( TO_DATE('28-11-1942') , 'fmdd' ) "Day Of Month" FROM dual ;
Day Of Month 28 SELECT TO_CHAR( TO_DATE('28-11-2942') , 'fmddd' ) "Day Of Year" FROM dual ;
Day of Year 332
8
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- oracle to bigquer y sql translat ion reference
- date functions create a stored procedure create
- transact sql reference
- programming with transact sql visual objects
- copyright © tutorialspoint
- to char function with dates
- t sql data types
- introduction claros data management hach
- sql server according to bob two guys who work on sql
- purpose of chapter is to show how welcome ict seneca