Oracle Datatype : Date (part III)
Posted by , Sep 23 2005, 10:47 PM
¡ÒÃáÊ´§¼ÅÇѹ·Õèã¹ÃٻẺµèÒ§ æ
¡ÒáÓ˹´ÃٻẺ·Õèµéͧ¡Òà ·Óä´éâ´Â¹ÓÊèǹ»ÃСͺÂèÍ (Date Format Elements ) µèÒ§ æ ÁÒ»ÃСͺ¡Ñ¹ Êèǹ»ÃСͺÂèÍÂàËÅèÒ¹Õéä´éá¡è
- Êèǹ»ÃСͺ·Õèá·¹ Çѹ à´×͹ »Õ áÅÐàÇÅÒµèÒ§ æ àªè¹ DD á·¹Çѹ·Õè MM á·¹à´×͹ à»ç¹µé¹
- à¤Ã×èͧËÁÒ¾ÔàÈɵèÒ§ æ ¤×Í / - : ; , .
- string ·ÕèÍÂÙèã¹à¤Ã×èͧËÁÒ¤Ӿٴ àªè¹ "Çѹ·Õè "
- modifier ÁÕÊͧµÑǤ×Í fm (fill mode) áÅÐ fx (format exact)
- suffix µèÍ·éÒ elements ä´éá¡è th áÅÐ sp (´ÙµÑÇÍÂèÒ§´éÒ¹ÅèÒ§)
µÑÇÍÂèÒ§·Õè 1 ¡ÒÃãªé quoted string áÅÐ fill mode modifier
CODE
SQL> select to_char(sysdate,'"Çѹ·Õè" dd "à´×͹"Month e yyyy') show_date from dual;
SHOW_DATE
----------------------------------------------
Çѹ·Õè 23 à´×͹¡Ñ¹ÂÒ¹ ÿ ÿ¾.È. 2548
SQL> select to_char(sysdate,'"Çѹ·Õè" dd "à´×͹"fmMonth e yyyy') show_date from dual;
SHOW_DATE
----------------------------------------------
Çѹ·Õè 23 à´×͹¡Ñ¹ÂÒ¹ ¾.È. 2548
¨Ò¡µÑÇÍÂèÒ§ ¨ÐàËç¹ÇèÒÁÕ¡ÒÃãªé Fill mode modifier ËÃ×Í fm «Ö觨ÐàË繤ÇÒÁᵡµèÒ§ ¤×ͪèͧÇèÒ§ËÅѧª×èÍà´×͹¨ÐàËÅ×Íà¾Õ§˹Ö觪èͧ ¤×ͪèͧ·ÕèàÃÒÃкØã¹ format model ¢³Ð·Õè¶éÒäÁèÃÐºØ fm ¨ÐÁÕ¡ÒÃà¼×èͪèͧÇèÒ§à¾×èÍãËéä´é¤ÇÒÁÂÒǵÑÇÍÑ¡ÉÃà·èҡѺª×èÍà´×͹·ÕèÂÒÇ·ÕèÊØ´SHOW_DATE
----------------------------------------------
Çѹ·Õè 23 à´×͹¡Ñ¹ÂÒ¹ ÿ ÿ¾.È. 2548
SQL> select to_char(sysdate,'"Çѹ·Õè" dd "à´×͹"fmMonth e yyyy') show_date from dual;
SHOW_DATE
----------------------------------------------
Çѹ·Õè 23 à´×͹¡Ñ¹ÂÒ¹ ¾.È. 2548
µÑÇÍÂèÒ§·Õè 2 ¡ÒÃãªé suffix
CODE
SQL> select to_char(sysdate, 'ddth') "ddth"
2 , to_char(sysdate, 'ddsp') "ddsp"
3 , to_char(sysdate, 'Ddsp') "Ddsp"
4 , to_char(sysdate, 'DDsp') "DDsp"
5 , to_char(sysdate, 'Ddspth') "Ddspth"
6 from dual;
ddth ddsp Ddsp DDsp Ddspth
---- ------------ ------------ ------------ --------------
23rd twenty-three Twenty-Three TWENTY-THREE Twenty-Third
¨Ò¡µÑÇÍÂèÒ§ th ¨ÐãªéáÊ´§ÅӴѺ·Õè Êèǹ sp ãªéá»Å§µÑÇàÅ¢à»ç¹µÑÇ˹ѧÊ×Í (spelling) â»Ã´ÊѧࡵؤÇÒÁᵡµèÒ§¢Í§¡ÒÃãªéµÑÇãËè-µÑÇàÅç¡·Õè dd ´éÇÂ2 , to_char(sysdate, 'ddsp') "ddsp"
3 , to_char(sysdate, 'Ddsp') "Ddsp"
4 , to_char(sysdate, 'DDsp') "DDsp"
5 , to_char(sysdate, 'Ddspth') "Ddspth"
6 from dual;
ddth ddsp Ddsp DDsp Ddspth
---- ------------ ------------ ------------ --------------
23rd twenty-three Twenty-Three TWENTY-THREE Twenty-Third
µÑÇÍÂèÒ§·Õè 3 format elements Í×è¹ æ ·ÕèãªéºèÍÂ
CODE
SQL> select to_char(sysdate, 'HH24:MI:SS') "Time"
2 from dual;
Time
--------
22:44:33
SQL> select to_char(sysdate, 'Day', 'nls_date_language=american') "Day of week"
2 , to_char(sysdate, 'Dy', 'nls_date_language=american') "Day of week (abbrv.)"
3 , to_char(sysdate, 'Day', 'nls_date_language=thai') "Day of week"
4 , to_char(sysdate, 'Dy', 'nls_date_language=thai') "Day of week (abbrv.)"
5 from dual;
Day of we Day Day of w Day
--------- --- -------- ---
Friday Fri ÈØ¡Ãì È.
SQL> select to_char(sysdate, 'd') "Day in week"
2 , to_char(sysdate, 'ddd') "Day in year"
3 , to_char(sysdate, 'q') "Quarter"
4 , to_char(sysdate, 'j') "Julian Date"
5 from dual;
D Day Q Julian
- --- - -------
6 266 3 2453637
2 from dual;
Time
--------
22:44:33
SQL> select to_char(sysdate, 'Day', 'nls_date_language=american') "Day of week"
2 , to_char(sysdate, 'Dy', 'nls_date_language=american') "Day of week (abbrv.)"
3 , to_char(sysdate, 'Day', 'nls_date_language=thai') "Day of week"
4 , to_char(sysdate, 'Dy', 'nls_date_language=thai') "Day of week (abbrv.)"
5 from dual;
Day of we Day Day of w Day
--------- --- -------- ---
Friday Fri ÈØ¡Ãì È.
SQL> select to_char(sysdate, 'd') "Day in week"
2 , to_char(sysdate, 'ddd') "Day in year"
3 , to_char(sysdate, 'q') "Quarter"
4 , to_char(sysdate, 'j') "Julian Date"
5 from dual;
D Day Q Julian
- --- - -------
6 266 3 2453637
Oracle Datatype : Date (part II)
Posted by , Sep 9 2005, 09:49 AM
¡ÒáÓ˹´ÃٻẺ¡ÒÃáÊ´§¼Å¢Í§¢éÍÁÙÅ»ÃÐàÀ·Çѹ·Õè
µèÍà¹×èͧ¨Ò¡ËÑÇ¢éÍ·ÕèáÅéÇ ÃٻẺ¡ÒèѴà¡çº¢éÍÁÙÅ»ÃÐàÀ·Çѹ·Õè ¨Ð¶Ù¡¨Ñ´à¡çºã¹ÃٻẺà´ÕÂǡѹ·Ñé§ËÁ´ áµèàÃÒÊÒÁÒöµÑ駤èÒ NLS parameters (National Language Support) à¾×èÍãËéâ»Ãá¡ÃÁáÊ´§¢éÍÁÙÅã¹ÃٻẺ·ÕèàÃÒµéͧ¡ÒÃä´é
ÇÔ¸Õ¡ÒáÓ˹´¤èÒ NLS parameters ÊÒÁÒö·Óä´é 4 ÃдѺ «Öè§ 3 ÃдѺááÊÒÁÒöÈÖ¡ÉÒä´é¨Ò¡º·¤ÇÒÁã¹ exzilla.net NLS Setting: How to set Oracle Database to work with Thai language ÃдѺÊØ´·éÒ·Õèà»ç¹ÃдѺÂèÍ·ÕèÊØ´ ¤×Í¡ÒáÓ˹´ã¹ÃдѺ function àªè¹ to_date ËÃ×Í to_char ´Ñ§µÑÇÍÂèÒ§
CODE
SQL> select to_char(sysdate, 'dd Mon yyyy',
ÿ2 ÿ ÿ ÿ'NLS_CALENDAR=GREGORIAN NLS_DATE_LANGUAGE=AMERICAN')
ÿ3 ÿfrom dual;
TO_CHAR(SYS
-----------
08 Sep 2005
SQL> select to_char(sysdate, 'dd Mon yyyy',
ÿ2 ÿ ÿ ÿ'NLS_CALENDAR=''Thai Buddha'' NLS_DATE_LANGUAGE=THAI')
ÿ3 ÿfrom dual;
TO_CHAR(SYSDA
-------------
08 ¡.Â. ÿ2548
SQL>
ËÁÒÂà赯 ÿ2 ÿ ÿ ÿ'NLS_CALENDAR=GREGORIAN NLS_DATE_LANGUAGE=AMERICAN')
ÿ3 ÿfrom dual;
TO_CHAR(SYS
-----------
08 Sep 2005
SQL> select to_char(sysdate, 'dd Mon yyyy',
ÿ2 ÿ ÿ ÿ'NLS_CALENDAR=''Thai Buddha'' NLS_DATE_LANGUAGE=THAI')
ÿ3 ÿfrom dual;
TO_CHAR(SYSDA
-------------
08 ¡.Â. ÿ2548
SQL>
¡ÒáÓ˹´ã¹ÃдѺ function ¹Õé ÊÓËÃѺ version 9i ãªéä´é੾ÒÐã¹»ÃÐ⤠SQL à·èÒ¹Ñé¹ äÁèÊÒÁÒöãªé¡Ñº PL/SQL functions ä´é ¶éÒµéͧ¡ÒÃãªé㹡ÒÃà¢Õ¹â»Ãá¡ÃÁ µéͧãªé
select to_char(date1,'dd/mm/yyyy','nls...') into variable from dual ;
à·èÒ¹Ñé¹ äÁèÊÒÁÒöãªé
variable := to_char(date1,'dd/mm/yyyy','nls...') ;
¡ÒáÓ˹´ parameters ã¹ÃдѺ session ºÒ§µÑÇ àªè¹ NLS_CALENDAR ¨ÐÁռšѺ parameter µÑÇÍ×è¹´éÇ ¨Ò¡µÑÇÍÂèÒ§¨ÐàËç¹ÇèÒàÁ×èÍ¡Ó˹´ NLS_CALENDAR ËÅѧ¡Ó˹´ NLS_DATE_FORMAT ¤èÒ·Õè¡Ó˹´äÇé¤ÃÑé§áá¨ÐËÒÂ仵éͧ¡Ó˹´ãËÁèÍÕ¡¤ÃÑé§
CODE
SQL> alter session set NLS_DATE_FORMAT='DD/MM/YYYY';
Session altered.
SQL> select sysdate from dual;
SYSDATE
----------
08/09/2005
SQL> alter session set NLS_CALENDAR=GREGORIAN;
Session altered.
SQL> select sysdate from dual;
SYSDATE
-----------
08 SEP 2005
SQL> alter session set NLS_DATE_FORMAT='DD/MM/YYYY';
Session altered.
SQL> select sysdate from dual;
SYSDATE
----------
08/09/2005
Session altered.
SQL> select sysdate from dual;
SYSDATE
----------
08/09/2005
SQL> alter session set NLS_CALENDAR=GREGORIAN;
Session altered.
SQL> select sysdate from dual;
SYSDATE
-----------
08 SEP 2005
SQL> alter session set NLS_DATE_FORMAT='DD/MM/YYYY';
Session altered.
SQL> select sysdate from dual;
SYSDATE
----------
08/09/2005
¹Í¡¨Ò¡¹Ñé¹ ¡ÒáÓ˹´¤èÒ੾Òкҧ¤èÒàªè¹ ¡Ó˹´ NLS_CALENDAR='Thai Buddha' ËÃ×Íà»ç¹»Õ ¾.È. àÃÒ¨ÐäÁèÊÒÁÒö NLS_DATE_LANGUAGE à»ç¹ÀÒÉÒÍ×蹹͡¨Ò¡ ÀÒÉÒä·Âä´é
CODE
SQL> alter session set NLS_CALENDAR=GREGORIAN;
Session altered.
SQL> alter session set NLS_DATE_FORMAT='DD fmMonth YYYY';
Session altered.
SQL> alter session set NLS_DATE_LANGUAGE=THAI;
Session altered.
SQL> select sysdate from dual;
SYSDATE
------------------
08 ¡Ñ¹ÂÒ¹ 2005
SQL> alter session set NLS_DATE_LANGUAGE=AMERICAN;
Session altered.
SQL> select sysdate from dual;
SYSDATE
-----------------
08 September 2005
SQL> alter session set NLS_CALENDAR='Thai Buddha';
Session altered.
SQL> alter session set NLS_DATE_FORMAT='DD fmMonth YYYY';
Session altered.
SQL> alter session set NLS_DATE_LANGUAGE=AMERICAN;
Session altered.
SQL> select sysdate from dual;
SYSDATE
------------------
08 ¡Ñ¹ÂÒ¹ 2548
Session altered.
SQL> alter session set NLS_DATE_FORMAT='DD fmMonth YYYY';
Session altered.
SQL> alter session set NLS_DATE_LANGUAGE=THAI;
Session altered.
SQL> select sysdate from dual;
SYSDATE
------------------
08 ¡Ñ¹ÂÒ¹ 2005
SQL> alter session set NLS_DATE_LANGUAGE=AMERICAN;
Session altered.
SQL> select sysdate from dual;
SYSDATE
-----------------
08 September 2005
SQL> alter session set NLS_CALENDAR='Thai Buddha';
Session altered.
SQL> alter session set NLS_DATE_FORMAT='DD fmMonth YYYY';
Session altered.
SQL> alter session set NLS_DATE_LANGUAGE=AMERICAN;
Session altered.
SQL> select sysdate from dual;
SYSDATE
------------------
08 ¡Ñ¹ÂÒ¹ 2548
à¡ÕèÂǡѺ¡ÒáÓ˹´ NLS parameters à·èÒ·Õè¹Ö¡ÍÍ¡¡çÁÕà·èÒ¹Õé¤ÃѺ ¤ÃÒÇ˹éÒ¨Ðà¢Õ¹µèÍà¡ÕèÂǡѺ format µèÒ§ æ ·ÕèãªéºèÍÂ
Oracle Datatype : Date (part I)
Posted by , Sep 7 2005, 09:12 PM
Oracle à¡çº¢éÍÁÙÅ»ÃÐàÀ·Çѹ·ÕèÍÂèÒ§äÃã¹°Ò¹¢éÍÁÙÅ
¨Ò¡¤ÇÒÁÃÙé·Õèä´é¨Ò¡¤Ø³ itachi áÅСÒà search à¾ÔèÁàµÔÁã¹àÇçº asktom
㹡ÒÃà¡çº¢éÍÁÙÅ·Õèà»ç¹Çѹ·Õèã¹µÒÃÒ§ oracle ¨Ðãªé¾×é¹·Õè 7 bytes â´ÂáµèÅÐ byte ÁÕ¤ÇÒÁËÁÒ´ѧ¹Õé
byte ·Õè 1 à¡çºµÑÇàÅ¢ÊͧËÅÑ¡áá¢Í§»Õ¤.È ºÇ¡´éÇ 100 à¾×èÍÃͧÃѺ¡ÒùѺ»Õ¡è͹¤ÃÔʵÈÑ¡ÃÒª
byte ·Õè 2 à¡çºµÑÇàÅ¢ÊͧËÅÑ¡ËÅѧ¢Í§»Õ¤.È ºÇ¡´éÇ 100 à¾×èÍÃͧÃѺ¡ÒùѺ»Õ¡è͹¤ÃÔʵÈÑ¡ÃÒª
byte ·Õè 3 à¡çºà´×͹ àªè¹ Á¡ÃÒ¤Á = 1 ¡ØÁÀҾѹ¸ì = 2
byte ·Õè 4 à¡çºÇѹ·Õè àªè¹ Çѹ·Õè 26 ¡ç¨Ðà¡çº 26
byte ·Õè 5 à¡çºàÇÅÒã¹Êèǹ¢Í§ªÑèÇâÁ§ ºÇ¡ 1
byte ·Õè 6 à¡çºàÇÅÒã¹Êèǹ¢Í§¹Ò·Õ ºÇ¡ 1
byte ·Õè 7 à¡çºàÇÅÒã¹Êèǹ¢Í§ÇÔ¹Ò·Õ ºÇ¡ 1
CODE
SQL> alter session set nls_calendar = 'Thai Buddha';
Session altered.
SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> create table test
ÿ2 ÿ( t1 date );
Table created.
SQL> insert into test values (to_date('05/10/2548 19:29:59'));
1 row created.
SQL> select dump(t1) from test;
DUMP(T1)
---------------------------------------------------------------
Typ=12 Len=7: 120,105,10,5,20,30,60
SQL>
Session altered.
SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> create table test
ÿ2 ÿ( t1 date );
Table created.
SQL> insert into test values (to_date('05/10/2548 19:29:59'));
1 row created.
SQL> select dump(t1) from test;
DUMP(T1)
---------------------------------------------------------------
Typ=12 Len=7: 120,105,10,5,20,30,60
SQL>
áµè¶éÒÂѧäÁèà¡çºã¹ table ¼ÅÅѾ¸ì·Õèä´é¨ÐᵡµèÒ§¡Ñ¹ ¶ÒÁ·ÕèàÇçº asktom ä´éÃѺ¤ÓµÍºÇèÒ à»ç¹ internal type ÊÓËÃѺµÑÇá»Ã (variable) «Öè§à¢ÒäÁè͸ԺÒÂà¾ÔèÁàµÔÁÇèÒáµèÅÐ byte ÁÕ¤ÇÒÁËÁÒÂÍÂèÒ§äà ¨Ò¡¡Ò÷´Êͺ´éÇÂÇѹà´×͹»ÕËÅÒÂ æ ªØ´ ¾ºÇèÒ¨Ò¡µÑÇàÅ¢Êͧ bytes áá¹ÓÁҤӹdz»Õ¤.Èä´é´éÇÂÊٵà 7*256+213 = 2005
CODE
SQL> select dump(to_date('05/10/2548 19:29:59')) from dual;
DUMP(TO_DATE('05/10/254819:29:59'))
------------------------------------------------------------
Typ=13 Len=8: 7,213,10,5,19,29,59,0
·´Êͺº¹ unix platformDUMP(TO_DATE('05/10/254819:29:59'))
------------------------------------------------------------
Typ=13 Len=8: 7,213,10,5,19,29,59,0
CODE
SQL> select dump(to_date('05/10/2548 19:29:59')) from dual;
DUMP(TO_DATE('05/10/254819:29:59'))
----------------------------------------------------------------
Typ=13 Len=8: 213,7,10,5,19,29,59,0
·´Êͺº¹ windows platformDUMP(TO_DATE('05/10/254819:29:59'))
----------------------------------------------------------------
Typ=13 Len=8: 213,7,10,5,19,29,59,0
¹èÒ¤Ô´ÇèÒ·ÓäÁ¨Ö§à¡çºã¹ÃٻẺ¹Õé á·¹·Õè¨Ðà¡çºà»ç¹ julian date
à˵ؼÅ˹Ö觹èҨСÒÃà¡çºÃٻẺ¹Õé ÊÒÁÒö convert ÁÒà»ç¹ÃٻẺ·ÕèãªéáÊ´§¼Åä´é§èÒ¡ÇèÒ ¤§äÁè¤èÍÂÁÕã¤ÃàÃÕ¡´Ù date ã¹ÃٻẺ julian date ¡Òäӹdz function trunc ËÃ×Í to_char ¨ÐÃÇ´àÃçÇ ¡ÒÃà»ÃÕºà·ÕºÁÒ¡¡ÇèÒ¹éÍ¡ÇèÒ¡ç·Óä´é·Ñ¹·Õ ËÒ¡¨ÐÁÕ¡Ò÷ӧҹ·Õè«Ñº«é͹¢Öé¹ ¡ç¤§à»ç¹ ¡ÒÃËҼŵèÒ§¢Í§àÇÅÒ àªè¹ ¡ÒÃËÒªèǧËèÒ§ÃÐËÇèÒ§Çѹ ·Õè¹ÓÇѹ·ÕèÊͧÇѹÁÒź¡Ñ¹µÃ§ æ Êèǹ months_between ËÃ×Í add_months ¡ÒÃà¡çºà´×͹¡Ñº»Õá¡ÍÍ¡ÁÒ¡ç¹èҨзÓãËé·Ó§Ò¹§èÒÂàªè¹à´ÕÂǡѹ
on Oracle Datatype : Date (part I)