Oracle Academy

Conversion Functions

Prof. Carmen PopescuOracle Academy Lead Adjunct

Slide 2 / 11

Conversion Functions

Slide 3 / 11

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

Slide 4 / 11

Conversion FunctionsDate -> Char

YYYYSelect to_char(sysdate,'dd-mon-YyYy') from dual=> 10-mar-2006

YYSelect to_char(sysdate,'dd-mon-YY') from dual=> 10-mar-06

Y Select to_char(sysdate,'dd-mon-Y') from dual=> 10-mar-6

YEARSelect to_char(sysdate,'dd-mon-Year') from dual=> 10-mar-Two Thousand SixSelect to_char(sysdate,'dd-mon-YEAR') from dual=> 10-mar-TWO THOUSAND SIX

Slide 5 / 11

Conversion FunctionsDate -> Char

MMSelect to_char(sysdate,'dd-mm-YYYY') from dual

=> 10-03-2006Select to_char(sysdate,'fmdd-mm-YYYY') from dual

=> 10-3-2006MON

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

Slide 6 / 11

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

Slide 7 / 11

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

Slide 8 / 11

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

Slide 9 / 11

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 of 2006

Slide 10 / 11

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 numereSELECT to_date('Ten March 2006','Ddsp March, YYYY')

=> eroare!

Slide 11 / 11

RR and YY format

Slide 12 / 11

RR and YY formatSelect 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')=>