Oracle SQL Data Conversion-Implicit Explicit
Oracle SQL Data Conversion Implicit Explicit
Online console used -- https://livesql.oracle.com
Implicit and Explicit data conversion
to_char, to_number, to_date and cast functions
Queries:
/*IMPLICIT DATA CONVERSION EXAMPLES*/
SELECT salary + '10' FROM hr.employees;
SELECT last_name FROM hr.employees WHERE employee_id = '200';
SELECT last_name FROM hr.employees WHERE employee_id = 200;
SELECT last_name FROM hr.employees WHERE hire_date = '03-MAR-97';
Create table birth(name varchar2(15), dob date);
Insert into birth values ('nids', '12/jan/58');
Insert into birth values ('panda', '14/feb/48');
Insert into birth values ('Spider', '14/feb/1958');
Insert into birth values ('agnes', '14/feb/2048');
Select * from birth;
SELECT to_date('12-jan-58') - to_date('14-feb-48') from DUAL;
'01/01/2058','MM/DD/RRRR'
01/01/2058
'01/01/2048','MM/DD/RRRR'
01/01/2048
'01/01/48','MM/DD/RRRR'
01/01/2048
'01/01/58','MM/DD/RRRR'
1/01/1958
'01/01/48','MM/DD/YYYY'
01/01/0048
'01/01/58','MM/DD/YYYY'
01/01/0058
'01/01/58','MM/DD/YY'
01/01/2058
'01/01/48','MM/DD/YY'
01/01/2048
SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
Alter session set nls_date_format = 'DD-MM-RRRR';
Select * from birth;
alter session set nls_date_format = 'DD-MON-YYYY';
Select * from birth;
ALTER session set nls_date_format = 'DD-MON-YY';
SELECT to_date('12-jan-58') - to_date('14-feb-48') from DUAL;
ALTER session set nls_date_format = 'DD-MON-RR';
/*EXPLICIT DATA CONVERSION EXAMPLES*/
/*TO_CHAR, TO_DATE, TO_NUMBER*/
SELECT first_name, TO_CHAR (hire_date, 'MON DD, YYYY') HIRE_DATE, TO_CHAR (salary, '$99999.99') Salary FROM hr.employees;
SELECT TO_CHAR('01110') FROM DUAL;
SELECT TO_CHAR('01110' + 1) FROM DUAL;
SELECT TO_DATE('2012-06-05', 'YYYY-MM-DD') FROM dual;
SELECT TO_DATE( '5 Jan 2017', 'DD MON YYYY' ) FROM dual;
SELECT TO_NUMBER('1210.73', '9999.99') from DUAL;
SELECT TO_NUMBER('$65.169', 'L99.999') FROM DUAL;
SELECT TO_NUMBER('123,456,789', '999,999,999') FROM DUAL;
/*This example takes a complicated string, determines that it is in certain format, and specifies the numeric characters to use*/
SELECT TO_NUMBER('$17 218,00', 'L999G999D00',' NLS_NUMERIC_CHARACTERS='', ''') FROM DUAL;
/*CAST*/
SELECT CAST( '22-Aug-2003' AS varchar2(30) ) from dual;
SELECT CAST('245.205' AS NUMBER(5,2)) FROM dual;
/* -- Result: 245.21 (note that the value is rounded, not truncated to 245.20) */
SELECT CAST(245.205 AS NUMBER(5,2)) FROM dual;
SELECT CAST ('123456' AS NUMBER) + 5 FROM DUAL;
SELECT CAST ('5.05.2017' AS DATE) +5 FROM DUAL;
/*check the default date format. That's why it give error*/
SELECT CAST ('30-APRIL-2015' AS DATE) +5 FROM DUAL;
SELECT CAST (SYSDATE AS TIMESTAMP) FROM DUAL;
SELECT SYSDATE FROM DUAL;
In questa pagina del sito puoi guardare il video online Oracle SQL Data Conversion-Implicit Explicit della durata di ore minuti seconda in buona qualità , che l'utente ha caricato Nids Dixit 25 novembre 2018, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 1,851 volte e gli è piaciuto 23 spettatori. Buona visione!
