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;
Auf dieser Seite können Sie das Online-Video Oracle SQL Data Conversion-Implicit Explicit mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer Nids Dixit 25 November 2018 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 1,851 Mal angesehen und es wurde von 23 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
