1 / 14

Oracle Academy

Oracle Academy. Conversion Functions. Conversion Functions. Conversion Functions Date -> Char. TO_CHAR (data, 'format') Cuvintele dintre ghilimele in format sunt inserate in sirul afisat exact cum apar Spatiile ce apar informat sunt reproduse in rezultatul afisat

novia
Download Presentation

Oracle Academy

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Academy Conversion Functions

  2. Conversion Functions

  3. Conversion FunctionsDate -> Char TO_CHAR (data, 'format') • Cuvintele dintre ghilimele in format sunt inserate in sirul afisat exact cum apar • Spatiile ce apar informat sunt reproduse in rezultatul afisat • fm – elimina spatiile sau zerourile nesemnificative SELECT to_char(sysdate,'dd "din luna" mm "a anului" YYYY') => 10 din luna 03 a anului 2006 SELECT to_char(sysdate,'fmdd "din luna" mm "a anului" YYYY') => 10 din luna 3 a anului 2006

  4. Conversion FunctionsDate -> Char YYYY Select to_char(sysdate,'dd-mon-YyYy') from dual => 10-mar-2006 YY Select to_char(sysdate,'dd-mon-YY') from dual => 10-mar-06 Y Select to_char(sysdate,'dd-mon-Y') from dual => 10-mar-6 YEAR Select to_char(sysdate,'dd-mon-Year') from dual => 10-mar-Two Thousand Six Select to_char(sysdate,'dd-mon-YEAR') from dual => 10-mar-TWO THOUSAND SIX

  5. Conversion FunctionsDate -> Char MM Select to_char(sysdate,'dd-mm-YYYY') from dual => 10-03-2006 Select to_char(sysdate,'fmdd-mm-YYYY') from dual => 10-3-2006 MON Select to_char(sysdate,'dd-mon-YYYY') from dual => 10-mar-2006 Select to_char(sysdate,'dd-MON-YYYY') from dual => 10-MAR-2006 Select to_char(sysdate,'dd-Mon-YYYY') from dual => 10-Mar-2006

  6. Conversion FunctionsDate -> Char MONTH Select to_char(sysdate,'dd-month-YYYY') from dual => 10-march-2006 Select to_char(sysdate,'dd-MONTH-YYYY') from dual => 10-MARCH-2006 Select to_char(sysdate,'dd-Month-YYYY') from dual => 10-March-2006

  7. Conversion FunctionsDate -> Char SP – se poate combina cu YYY, DD, MM, HH, MI Select to_char(sysdate,'dd-mm-YYYY') from dual => 10-03-2006 Select to_char(sysdate,'ddsp-mm-YYYY') from dual => ten-03-2006 Select to_char(sysdate,'ddspth-mm-YYYY') from dual => tenth-03-2006 Select to_char(sysdate-7,'ddspth-mm-YYYY') from dual => third-03-2006

  8. Conversion FunctionsDate -> Char DD - ziua din luna (1..31) Select to_char(sysdate,'dd-mm-YYYY') from dual => 10-03-2006 DDth DDTH (the spell is set by DD not the TH) Select to_char(sysdate,'DDth-mm-YYYY') from dual => 10TH-03-2006 Ddth DdTH Select to_char(sysdate,'DdTH-mm-YYYY') from dual => 10Th-03-2006 ddth ddTH Select to_char(sysdate,'ddTH-mm-YYYY') from dual => 10th-03-2006

  9. Conversion FunctionsDate -> Char D – ziua din saptamana (1..7) Select to_char(sysdate,'d-mm-YYYY') from dual => 6-03-2006 DAY Select to_char(sysdate,'Day, dd-mm-YYYY') from dual => Friday, 10-03-2006 DY – abrevierea cu trei caractere Select to_char(sysdate,'DY, dd-mm-YYYY') from dual => FRI, 10-03-2006 DDD – ziua din an (1..366) Select to_char(sysdate,'ddd "of" YYYY') from dual => 69 of2006

  10. Conversion FunctionsChar -> Date TO_DATE('data', 'format') • Se folosesc aceleasi formate ca si la TO_CHAR cu urmatoarele restrictii: • Nu se pot folosi siruri incluse intre ghilimele: SELECT to_date('10 of March 2006','dd "of" March, YYYY') => eroare! • Zilele nu pot fi scrise cu litere, trebuie sa fie numere SELECT to_date('Ten March 2006','Ddsp March, YYYY') => eroare!

  11. RR and YY format

  12. RR and YY format Select TO_CHAR(TO_DATE('01-01-97','dd-mm-yy'),'YYYY') => Select TO_CHAR(TO_DATE('01-01-97','dd-mm-rr'),'RRR') => Select TO_CHAR(TO_DATE('01-01-97','dd-mm-rr'),'YYYY') => Select TO_CHAR(TO_DATE('01-01-97','dd-mm-yy'),'RRR') =>

  13. NVL,NVL2,NULLIF, COALESCE NVL ( val1, val2 )- returneaza valoarea val1, daca aceasta este nenula, iar daca val1 este null atunci va returna val2 NVL2 (val1, val2 ,val3)- daca valoarea val1 nu este nula, atunci functia va returna valoarea val2, iar daca val1 are valoarea null, atunci functia va returna valoarea val3 NULLIF(expression 1, expression 2)- daca cele doua expresii sunt egale, functia returneaza null. Dacă valorile celor doua sunt diferite atunci functia va returna valoarea primei expresii. COALESCE(expression 1, expression 2, ...expression n)- functia returneaza valoarea primei expresii nenule

  14. EXPRESII CONDITIONALE DECODE(expression, search1,result1 [, search2, result2,...,] [, default]) Compara valoarea expresiei cu search1,2,….Daca valorile sunt egale functia returneaza result1,2….Daca functia nu este egala cu nici o valoare va returna valoarea default.

More Related