SQL> SELECT TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'DD-
--------------------
01-FEB-2011 15:24:12
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, 'DD-MON-RRRR HH24:MI:SS' FROM DUAL
SQL> /
SYSDATE 'DD-MON-RRRRHH24:MI:SS
--------- ----------------------
01-FEB-11 DD-MON-RRRR HH24:MI:SS
1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') FROM DUAL
SQL> /
TO_CHAR(S
---------
01-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') "SYSTEM DATE" FROM DUAL
SQL> /
SYSTEM DA
---------
01-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT TO_CHAR(SYSDATE,'DD-MON-RR') "SYS DATE" FROM DUAL
SQL> /
SYS DATE
---------
01-FEB-11
1* SELECT SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) "MONTH-B/W" FROM EMP
SQL> /
SYSDATE HIREDATE MONTH-B/W
--------- --------- ----------
01-FEB-11 17-DEC-80 361.504772
01-FEB-11 20-FEB-81 359.407998
01-FEB-11 22-FEB-81 359.343482
01-FEB-11 02-APR-81 357.988643
01-FEB-11 28-SEP-81 352.149934
01-FEB-11 01-MAY-81 357
01-FEB-11 09-JUN-81 355.762837
01-FEB-11 19-APR-87 285.440256
01-FEB-11 17-NOV-81 350.504772
01-FEB-11 08-SEP-81 352.795095
01-FEB-11 23-MAY-87 284.311224
01-FEB-11 03-DEC-81 349.956385
01-FEB-11 03-DEC-81 349.956385
01-FEB-11 23-JAN-82 348.311224
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1* SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W" FROM EMP
SQL> /
SYSDATE HIREDATE MONTH-B/W
--------- --------- ----------
01-FEB-11 17-DEC-80 362
01-FEB-11 20-FEB-81 359
01-FEB-11 22-FEB-81 359
01-FEB-11 02-APR-81 358
01-FEB-11 28-SEP-81 352
01-FEB-11 01-MAY-81 357
01-FEB-11 09-JUN-81 356
01-FEB-11 19-APR-87 285
01-FEB-11 17-NOV-81 351
01-FEB-11 08-SEP-81 353
01-FEB-11 23-MAY-87 284
01-FEB-11 03-DEC-81 350
01-FEB-11 03-DEC-81 350
01-FEB-11 23-JAN-82 348
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* (SYSDATE-HIREDATE)/30 "MONTHSSS" FROM EMP
SQL> /
SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 366.788296
01-FEB-11 20-FEB-81 359 364.621629
01-FEB-11 22-FEB-81 359 364.554963
01-FEB-11 02-APR-81 358 363.254963
01-FEB-11 28-SEP-81 352 357.288296
01-FEB-11 01-MAY-81 357 362.288296
01-FEB-11 09-JUN-81 356 360.988296
01-FEB-11 19-APR-87 285 289.654963
01-FEB-11 17-NOV-81 351 355.621629
01-FEB-11 08-SEP-81 353 357.954963
01-FEB-11 23-MAY-87 284 288.521629
01-FEB-11 03-DEC-81 350 355.088296
01-FEB-11 03-DEC-81 350 355.088296
01-FEB-11 23-JAN-82 348 353.388296
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* ROUND((SYSDATE-HIREDATE)/30,0) "MONTHSSS" FROM EMP
SQL> /
SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 367
01-FEB-11 20-FEB-81 359 365
01-FEB-11 22-FEB-81 359 365
01-FEB-11 02-APR-81 358 363
01-FEB-11 28-SEP-81 352 357
01-FEB-11 01-MAY-81 357 362
01-FEB-11 09-JUN-81 356 361
01-FEB-11 19-APR-87 285 290
01-FEB-11 17-NOV-81 351 356
01-FEB-11 08-SEP-81 353 358
01-FEB-11 23-MAY-87 284 289
01-FEB-11 03-DEC-81 350 355
01-FEB-11 03-DEC-81 350 355
01-FEB-11 23-JAN-82 348 353
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE),0) "MONTH-B/W",
2* ROUND((SYSDATE-HIREDATE)/30,0) "MONTHSSS" FROM EMP
SQL> /
SYSDATE HIREDATE MONTH-B/W MONTHSSS
--------- --------- ---------- ----------
01-FEB-11 17-DEC-80 362 367
01-FEB-11 20-FEB-81 359 365
01-FEB-11 22-FEB-81 359 365
01-FEB-11 02-APR-81 358 363
01-FEB-11 28-SEP-81 352 357
01-FEB-11 01-MAY-81 357 362
01-FEB-11 09-JUN-81 356 361
01-FEB-11 19-APR-87 285 290
01-FEB-11 17-NOV-81 351 356
01-FEB-11 08-SEP-81 353 358
01-FEB-11 23-MAY-87 284 289
01-FEB-11 03-DEC-81 350 355
01-FEB-11 03-DEC-81 350 355
01-FEB-11 23-JAN-82 348 353
1* SELECT SYSDATE, NEXT_DAY(SYSDATE,'WEDNESDAY') FROM DUAL
SQL> /
SYSDATE NEXT_DAY(
--------- ---------
01-FEB-11 02-FEB-11
1* SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL
SQL> /
SYSDATE LAST_DAY(
--------- ---------
01-FEB-11 28-FEB-11
SQL> -- LAST_DAY,, RETURNS LASTDAY OF THE MONTH ----
SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2) FROM DUAL;
SYSDATE ADD_MONTH
--------- ---------
01-FEB-11 01-APR-11
1* SELECT SYSDATE, ADD_MONTHS(SYSDATE, -2) FROM DUAL
SQL> /
SYSDATE ADD_MONTH
--------- ---------
01-FEB-11 01-DEC-10
SQL> SELECT '12' * 5, '01-FEB-11' FROM DUAL;
'12'*5 '01-FEB-1
---------- ---------
60 01-FEB-11
SQL> ED
Wrote file afiedt.buf
1* SELECT '12' * 5, '01-FEB-11', '3*5' FROM DUAL
SQL> /
'12'*5 '01-FEB-1 '3*
---------- --------- ---
60 01-FEB-11 3*5
SQL> ED
Wrote file afiedt.buf
1* SELECT '12' * 5, '01-FEB-11', 3*'5' FROM DUAL
SQL> /
'12'*5 '01-FEB-1 3*'5'
---------- --------- ----------
60 01-FEB-11 15
SQL> -- CONVERSION FUNCTION,,, 'IGNORES SINGLE CODE'-----
SQL> -- ALSO CALLED IMPLICIT CONVERSION --
1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW') "FORMATS" FROM DUAL
SQL> /
SYSDATE FORMATS
--------- ----------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051
SQL> ED
Wrote file afiedt.buf
1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW') "FORMATS" FROM DUAL
SQL> /
SYSDATE FORMATS
--------- ----------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051
SQL> ED
Wrote file afiedt.buf
1* select SYSDATE, TO_CHAR(SYSDATE, 'D, DD, DDD, DAY, DY, W, WWW, MM, MON, MONTH, RM') "FORMATS" FROM DUAL
SQL> /
SYSDATE FORMATS
--------- ------------------------------------------------------------
01-FEB-11 3, 01, 032, TUESDAY , TUE, 1, 051, 02, FEB, FEBRUARY , II
SQL> ed
Wrote file afiedt.buf
1* select SYSDATE, TO_CHAR(SYSDATE, 'DD-MM-RRRR SCC,CC') "FORMATS" FROM DUAL
SQL> /
SYSDATE FORMATS
--------- -----------------
01-FEB-11 01-02-2011 21,21
SQL> ED
Wrote file afiedt.buf
1* select SYSDATE, TO_CHAR(SYSDATE, 'DD-MM-RRRR SCC AD,CC B.C.') "FORMATS" FROM DUAL
SQL> /
SYSDATE FORMATS
--------- -------------------------
01-FEB-11 01-02-2011 21 AD,21 A.D.
SQL> -- TIME FORMAT---
No comments:
Post a Comment