| programmera.net -> oracle -> normal för utskrift | info@programmera.net |
Datumfunktioner i Oracle
|
Funktioner som SYSDATE, CURRENT_DATE, LOCALTIMESTAMP, SYSTIMESTAMP och CURRENT_TIMESTAMP är funktioner utan parametrar. Datatypen TIMESTAMP är ny för Oracle 9i, och det är även alla funktioner som behandlar denna datatyp.
SQL> SELECT sysdate, to_char(sysdate,'YYYY-MM-DD HH24:MI') FROM dual; SYSDATE TO_CHAR(SYSDATE, ---------- ---------------- 2005-01-18 2005-01-18 22:17 |
SQL> SELECT current_date, to_char(current_date,'YYYY-MM-DD HH24:MI') FROM dual; CURRENT_DA TO_CHAR(CURRENT_ ---------- ---------------- 2005-01-18 2005-01-18 22:18 |
SQL> SELECT localtimestamp FROM dual; LOCALTIMESTAMP --------------------------------------------------------------------------- 2005-01-18 22:38:48,922000 |
SQL> SELECT systimestamp FROM dual; SYSTIMESTAMP --------------------------------------------------------------------------- 2005-01-18 22:43:14,223000 +01:00 |
SQL> SELECT current_timestamp FROM dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 2005-01-18 22:39:25,194000 +01:00 |
SQL> SELECT TO_CHAR(sysdate,'YYYY/MM/DD') as str_date ,TO_CHAR(systimestamp,'YYYY/MM/DD') as str_ts FROM dual; STR_DATE STR_TS ---------- ---------- 2005/01/18 2005/01/18 |
| Format | Beskrivning | |
|---|---|---|
| YY RR YYYY RRRR YEAR | År i kort form, lång form och beskrivning. | |
| Q | Kvartal | |
| MM MON MONTH | Olika former av måndad. | |
| WW W | 1-52 vecka per år, 1-4 vecka per månad. | |
| D DD DY DAY | 1-7 veckodag, 1-31 månadsdag, 1-366 dag på året, dagens namn. | |
| HH HH12 HH24 | Timmar | |
| MI | Minuter | |
| SS SSSSS | Sekunder | |
| SS.FF1 till SS.FF9 | Sekunder med olika många decimaler (endast för TIMESTAMP). |
SQL> SELECT sysdate, TO_CHAR(sysdate,'YY RR YYYY RRRR YEAR: Q: MM MON MONTH: WW W') as format1 FROM dual; SYSDATE FORMAT1 ---------- ------------------------------------------------------------------------------------- 2005-01-18 05 05 2005 2005 TWO THOUSAND FIVE: 1: 01 JAN JANUARI : 03 3 SQL> SELECT sysdate, TO_CHAR(sysdate,'D DD DY DAY: HH HH12 HH24: MI: SS SSSSS') as format1 FROM dual; SYSDATE FORMAT1 ---------- ---------------------------------------- 2005-01-18 2 18 TIS TISDAG : 11 11 23: 40: 47 85247 |
SQL> SELECT systimestamp, TO_CHAR(systimestamp,'SS SS.FF3') as format1 FROM dual; SYSTIMESTAMP FORMAT1 --------------------------------------------------------------------------- --------------- 2005-01-18 23:44:54,033000 +01:00 54 54.033 |
SQL> SELECT TO_DATE('2004/12/01 10:21','YYYY/MM/DD HH24:MI') FROM dual;
TO_DATE('2
----------
2004-12-01
|
SQL> SELECT TO_TIMESTAMP('2004/12/01 10:21','YYYY/MM/DD HH24:MI') FROM dual;
TO_TIMESTAMP('2004/12/0110:21','YYYY/MM/DDHH24:MI')
---------------------------------------------------------------------------
2004-12-01 10:21:00,000000000
|
EXTRACT( format FROM {datum | timestamp})
|
| Format | Beskrivning | |
|---|---|---|
| YEAR | =2005 | |
| MONTH | =1 | |
| DAY | =18 | |
| HOUR | =20 | |
| MINUTE | =30 | |
| SECOND | =41,024 | |
| TIMEZONE_HOUR | 1 | |
| TIMEZONE_MINUTE | 0 | |
| TIMEZONE_REGION | UNKNOWN | |
| TIMEZONE_ABBR | UNK |
SQL> SELECT current_timestamp ,EXTRACT(YEAR FROM current_timestamp) as year ,EXTRACT(MONTH FROM current_timestamp) as mon ,EXTRACT(DAY FROM current_timestamp) as day ,EXTRACT(HOUR FROM current_timestamp) as hour ,EXTRACT(MINUTE FROM current_timestamp) as min ,EXTRACT(SECOND FROM current_timestamp) as sec ,EXTRACT(TIMEZONE_HOUR FROM current_timestamp) as tzh ,EXTRACT(TIMEZONE_MINUTE FROM current_timestamp) as tzmin ,EXTRACT(TIMEZONE_REGION FROM current_timestamp) as tzreg ,EXTRACT(TIMEZONE_ABBR FROM current_timestamp) as tzabb FROM dual; CURRENT_TIMESTAMP YEAR MON DAY HOUR MIN SEC TZH TZMIN TZREG TZABB ----------------------------------- ----- ----- ----- ----- ----- ---------- ----- ----- ------------ ---------- 2005-01-18 21:30:41,024000 +01:00 2005 1 18 20 30 41,024 1 0 UNKNOWN UNK |
SQL> SELECT EXTRACT(MONTH FROM DATE '2005-11-10') as mon ,EXTRACT(MINUTE FROM TIME '22:54:01') as min FROM dual; MON MIN ----- ----- 11 54 |
SQL> SELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI') as the_date ,to_char(ROUND(sysdate), 'YYYY-MM-DD HH24:MI') as default_val ,to_char(ROUND(sysdate,'CC'), 'YYYY-MM-DD HH24:MI') as century ,to_char(ROUND(sysdate,'YEAR'), 'YYYY-MM-DD HH24:MI') as year ,to_char(ROUND(sysdate,'MONTH'), 'YYYY-MM-DD HH24:MI') as mom ,to_char(ROUND(sysdate,'DDD'), 'YYYY-MM-DD HH24:MI') as day ,to_char(ROUND(sysdate,'HH24'),'YYYY-MM-DD HH24:MI') as hour FROM dual; THE_DATE DEFAULT_VAL CENTURY YEAR MOM DAY HOUR ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- 2005-01-18 22:10 2005-01-19 00:00 2001-01-01 00:00 2005-01-01 00:00 2005-02-01 00:00 2005-01-19 00:00 2005-01-18 22:00 |
SQL> SELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI') as the_date ,to_char(TRUNC(sysdate), 'YYYY-MM-DD HH24:MI') as default_val ,to_char(TRUNC(sysdate,'CC'), 'YYYY-MM-DD HH24:MI') as century ,to_char(TRUNC(sysdate,'YEAR'), 'YYYY-MM-DD HH24:MI') as year ,to_char(TRUNC(sysdate,'MONTH'), 'YYYY-MM-DD HH24:MI') as mom ,to_char(TRUNC(sysdate,'DDD'), 'YYYY-MM-DD HH24:MI') as day ,to_char(TRUNC(sysdate,'HH24'),'YYYY-MM-DD HH24:MI') as hour FROM dual; THE_DATE DEFAULT_VAL CENTURY YEAR MOM DAY HOUR ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- 2005-01-18 22:13 2005-01-18 00:00 2001-01-01 00:00 2005-01-01 00:00 2005-01-01 00:00 2005-01-18 00:00 2005-01-18 22:00 |
SQL> SELECT NEXT_DAY(sysdate,4) FROM dual; NEXT_DAY(S ---------- 2005-01-20 |
SQL> SELECT LAST_DAY(sysdate) FROM dual; LAST_DAY(S ---------- 2005-01-31 |
SQL> SELECT ADD_MONTHS(sysdate, 3) FROM dual; ADD_MONTHS ---------- 2005-04-19 |
SQL> SELECT MONTHS_BETWEEN('04-JAN-04','05-JAN-05') FROM dual;
MONTHS_BETWEEN('04-JAN-04','05-JAN-05')
---------------------------------------
-12,032258
|