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')=>
Top Related