Two Types of Functions:
-----------------------
SINGLE ROW FUNCTIONS: Returns one result per row
MULTIPLE ROW FUNCTIONS/AGGREGATE FUNCTIONS: Returns one
result per set of rows
Single Row Function:
--------------------
Character Functions: That modify character value
Number functions: that work on numeric data
Date functions: working with date values
Conversion Functions: converts from one data type to another
General Functions: useful to treat null values.
Character Functions:
===================
SELECT 'Welcome to Oracle' Greeting from dual;
SELECT UPPER('Welcome to Oracle') Greeting from dual;
SELECT last_name FROM hr.employees;
SELECT last_name, UPPER(last_name) uname FROM hr.employees;
SELECT ENAME FROM EMP;
SELECT ENAME, LOWER(ENAME) lname FROM emp;
SELECT LOWER('ITAMERICA') FROM DUAL;
SELECT * FROM EMP WHERE JOB = 'salesman';
SELECT * FROM EMP WHERE LOWER(JOB) = 'salesman';
SELECT ENAME FROM EMP;
select ENAME, INITCAP(ename) from emp;
SELECT initcap('welcome to oracle sql') Greeting from dual;
ASCII - AMERICAN STANDARD CODE FOR INFORMATION INTERCHANGE
select ASCII('A') from dual;
SELECT ascii('a') FROM dual;
select ascii('B') from dual;
select ascii('Z') from dual;
select ascii('AB') from dual;
SELECT ASCII('A')||' '||ASCII('B') FROM DUAL;
SELECT ASCII(' ') FROM DUAL;
SELECT ASCII('.') FROM DUAL;
select CHR(65) from dual;
SELECT CHR(92) FROM DUAL;
SELECT CHR(1) FROM DUAL;
SELECT CHR(2) FROM DUAL;
SELECT CHR(3) FROM DUAL;
SELECT 'ORACLE' FROM DUAL;
select LENGTH('Oracle') from dual;
select ENAME, length(ename) from emp;
BIT
8 BITS = 1 BYTE
1024 BYTES = 1 KILO BYTE(1 KB)
1024 KILO BYTE = 1 MEGA BYTE(1 MB)
1024 MEGA BYTE = 1 GIGA BYTE (1 GB)
A - 65
2 65
32 - 1
16 - 0
08 - 0
04 - 0
02 - 0
1 - 0
01000001
select LENGTHB('Oracle') from dual;
select length('???') from dual;
select lengthb('???') from dual;
select length('????') from dual;
select lengthb('????') from dual;
select REVERSE('Oracle') from dual;
SELECT LENGTH('
Oracle '), LENGTH('Oracle') FROM dual;
SELECT ' Oracle ', trim('
Oracle ') col FROM dual;
SELECT ltrim(' Oracle
') col FROM dual;
SELECT ltrim('AOracleA') col FROM dual;
SELECT LTRIM('AOracleA', 'AO') col FROM DUAL;
SELECT LTRIM('AOracleA', 'ac') col FROM DUAL;
SELECT rtrim(' Oracle
') col FROM dual;
SELECT rtrim('AOracleA', 'A') col FROM dual;
SELECT rtrim('AOracleA', 'Ae') col FROM dual;
SELECT LTRIM('AORACLEA','A') AS col1dsfdsf FROM DUAL;
SELECT LTRIM('AAAORACLEA','A') AS col1dsfdsf FROM DUAL;
SELECT LTRIM('ABAAORACLEA','A') AS col1dsfdsf FROM DUAL;
SELECT LTRIM('AAAORACLEA','a') AS col1dsfdsf FROM DUAL;
SELECT LTRIM('XYZXYZOracle','XYZ') FROM DUAL;
SELECT LTRIM('XYZXZYOracle','XYZ') FROM DUAL;
SELECT LTRIM('XYZAXZYOracle','XYZ') FROM DUAL;
SELECT RTRIM('XYZOracleXYZXYZ','XYZ') FROM DUAL;
SELECT RTRIM('xyzOracleXYzXY','XYZ') FROM DUAL;
SELECT LTRIM(RTRIM('XYZOracleXYZXYZ','XYZ'), 'XYZ') AS "trim" FROM DUAL;
Note : WRITING ONE SINGLE ROW FUNCTION WITHIN ANOTHER SINGLE
ROW FUNCTION IS
KNOWN AS
NESTED FUNCTION. Single row function we can nest upto any level.
SYNTAX: TRIM([BOTH/LEADING/TRAILING] [<CHARACTER>
FROM] <STRING>);
-------
SELECT ' Oracle
' FROM DUAL;
SELECT TRIM(' Oracle
') col1 FROM DUAL;
SELECT TRIM(BOTH '
Oracle ') col1 FROM DUAL; -- will give error
SELECT TRIM(BOTH
FROM ' Oracle ') col1 FROM DUAL;
SELECT TRIM(' ' FROM
' Oracle ') col1 FROM DUAL;
SELECT TRIM(BOTH ' '
FROM ' Oracle ') col1 FROM DUAL;
SELECT TRIM('AOracleA')
col1 FROM DUAL;
SELECT TRIM('E' FROM
'EOraclE') FROM DUAL;
SELECT TRIM(BOTH 'E'
FROM 'EOraclE') FROM DUAL;
SELECT TRIM('E' FROM
'EEOraclEE') FROM DUAL;
SELECT TRIM('EA'
FROM 'EAOraclEA') FROM DUAL; -- gives error
SELECT TRIM('A' FROM
'EAOraclEA') FROM DUAL;
SELECT TRIM(both
'EO' FROM 'EOraclEO') FROM DUAL; -- WILL GIVE ERROR
SELECT TRIM(TRAILING
'E' FROM 'EEOraclE') FROM DUAL;
SELECT TRIM(LEADING
'E' FROM 'EEOraclE') FROM DUAL;
SELECT ENAME, JOB
FROM EMP;
SELECT
CONCAT(ename,job) FROM emp;
SELECT CONCAT(ename,
'ITAMERICA') FROM emp;
SELECT CONCAT(ename,
' ITAMERICA') FROM emp;
SELECT
concat(concat('scott ','salesman '),1001) FROM DUAL;
select concat('scott
', 'salesman 1001') from dual;
SELECT
concat(concat(ename,job),empno) FROM emp;
SELECT CONCAT(ename,
job) FROM emp;
SELECT
CONCAT(CONCAT(ename||' ', job),' '||empno) FROM emp;
SELECT * FROM emp
WHERE CONCAT(ENAME,JOB)='SCOTT'||'ANALYST';
SELECT
CONCAT(CONCAT(ENAME,' IS EARNING SALARY $'), SAL) INFO FROM EMP;
SELECT
CONCAT(ename,CONCAT(' ',job)) FROM emp;
SELECT REPLACE('Oracle','O','Mi') FROM DUAL;
SELECT REPLACE('Oracle','Or','Mii') FROM DUAL;
SELECT REPLACE('Oracle','c','k') FROM DUAL;
SELECT REPLACE('Oracle','cl','k') FROM DUAL;
SELECT REPLACE('Oracle','cL','k') FROM DUAL;
SELECT REPLACE('Oracle','a') FROM DUAL;
SELECT REPLACE('Oracle','ce','ka') FROM DUAL;
SELECT REPLACE('Oracle','le','ka') FROM DUAL;
SELECT REPLACE('Oracle','Oc','Mk') FROM DUAL; -- WILL NOT
REPLACE
SELECT TRANSLATE('Oracle','Oc','Mk') FROM DUAL;
SELECT TRANSLATE('Oracle','O','Mi') FROM DUAL;
SELECT TRANSLATE('Oracle','cl','k') FROM DUAL;
SELECT TRANSLATE('Oracle','za','z') FROM DUAL;
SELECT TRANSLATE('Oracle','ce','ka') FROM DUAL;
INSTR function: it is useful to find out the character
position number in the
string.
Syntax:
-------
INSTR('string'/column,
<'character'>,[<search_postion>],[<character_occurence>])
SELECT instr('Our new office is near to train station','o',1,1)
AS pos
FROM dual;
SELECT instr('Our new office is near to train
station','new',1) AS pos
FROM dual;
SELECT instr('Our new office is near to train
station','r',2) AS pos
FROM dual;
SELECT instr('Our new office is near to train station','r')
AS pos FROM dual;
SELECT instr('Our new office is near to train
station','r',4) AS pos
FROM dual;
SELECT instr('Our new office is near to train
station','r',23,1) AS pos
FROM dual;
SELECT instr('Our new office is near to train
station','f',1,2) AS pos
FROM dual;
SELECT instr('Our new office is near to train
station','o',1,3) AS pos
FROM dual;
SELECT instr('Our new office is near to train
station','i',-1,1) AS pos
FROM dual;
SELECT instr('Our new office is near to train
station','i',-4,3) AS pos
FROM dual;
SELECT instr('Our new office is near to train
station','z',1) AS pos
FROM dual;
SUBSTR : returns the substring from the string.
Syntax:
-------
SUBSTR('string'/column,start_position,no_of_characters_to_return);
SELECT SUBSTR('oracle',2) FROM dual;
SELECT SUBSTR('oracle',3,4) FROM DUAL;
SELECT SUBSTR('oracle',2,2) FROM DUAL;
SELECT SUBSTR('oracle',-2) FROM dual;
SELECT SUBSTR('oracle',-4,3) FROM dual;
SELECT SUBSTR('oracle',2,4) FROM dual;
SELECT SUBSTR('oracle',5,4) c1 FROM dual;
SELECT SUBSTR('oracle',-2,3) c2 FROM dual;
SELECT ename, SUBSTR(ename,3) FROM emp;
SELECT ename, SUBSTR(ename,2,3) FROM emp;
SELECT ENAME, SUBSTR(ENAME, -1, 1) SUB_STR FROM EMP;
SELECT ENAME FROM EMP;
SELECT ename FROM emp WHERE SUBSTR(ename, -1,1)='N';
SELECT ENAME FROM EMP WHERE ENAME LIKE '%N';
DROP TABLE strdemo;
CREATE TABLE strdemo(id NUMBER, name VARCHAR2(50));
SELECT * FROM STRDEMO;
INSERT INTO strdemo VALUES(1,'Nishi;S;Sheth');
INSERT INTO strdemo VALUES(2,'Ranjana;N;Pathak');
INSERT INTO strdemo VALUES(3,'Santhosh;Rohan;Surabhi');
INSERT INTO strdemo VALUES(4,'Ravee;R;Bhambri');
INSERT INTO strdemo VALUES(5,'Shuchita;M;Syed');
COMMIT;
SELECT * FROM STRDEMO;
SELECT NAME, SUBSTR(NAME,1, INSTR(NAME, ';',1,1)-1)
FIRST_NAME,
SUBSTR(NAME, INSTR(NAME, ';', 1, 1)+1, INSTR(NAME,
';',1,2)-1-INSTR(NAME, ';', 1, 1)) MIDDLE_NAME,
SUBSTR(NAME, INSTR(NAME, ';',1,2)+1) AS LAST_NAME
FROM STRDEMO;
SYNTAX:
LPAD(DATA/COLUMN, TOTAL_NO_CHARACTERS, ['CHARACTER TO BE
PADDED']).
SELECT sal FROM emp;
SELECT LPAD(sal, 10, '*') left_pad FROM emp;
SELECT SAL, LPAD(SAL,10) LP FROM EMP;
SELECT RPAD(sal, 10,
'*') right_pad FROM emp;
SELECT RPAD(LPAD(sal, 10, '*'), 17, '*') FROM emp;
SELECT lpad('oracle',6) COL1, lpad('oracle',8) COL2 FROM dual;
SELECT lpad('oracle',6,'*') col1, lpad('oracle',8,'*') col2
FROM dual;
SELECT lpad('oracle',8,'a') FROM dual;
SELECT lpad('oracle',8,'ab') FROM dual;
SELECT lpad('oracle',9,'ab') FROM dual;
SELECT LPAD('CORP',6,'S') FROM DUAL;
SELECT LPAD('CORP',6,'PQR') FROM DUAL;
SELECT LPAD('CORP') col FROM DUAL;
SELECT LPAD('CORP',3) col FROM DUAL;
SELECT RPAD('corp',5,'*') FROM DUAL;
SELECT LPAD(ENAME,10) col FROM EMP;
Date Functions
==============
SELECT SYSDATE FROM DUAL; -- it will display database
current date in date datatype format
DD-MON-RR
SELECT SYSTIMESTAMP FROM DUAL; -- will display system date
and time on which database is running.
DD-MON-RR HH:MI:SS.SSSSS AM/PM TIME_ZONE/REGION_NAME
SELECT CURRENT_DATE FROM DUAL; -- it will display current
date of session running.
DD-MON-RR
SELECT CURRENT_TIMESTAMP FROM DUAL; -- it will display
current date and time of session.
-- it will
display using timestamp with time zone datatpe.
DD-MON-RR HH:MI:SS.SSSSS AM/PM TIME_ZONE/REGION_NAME
SELECT LOCALTIMESTAMP FROM DUAL; -- it will display current
date and time of the session
-- it will in
the timestamp with local time zone datatype format.
DD-MON-RR HH:MI:RR AM/PM:
SELECT SYSDATE, SYSTIMESTAMP, CURRENT_DATE,
CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;
ALTER SESSION SET TIME_ZONE='+5:30';
SELECT SYSDATE, SYSTIMESTAMP, CURRENT_DATE,
CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;
ALTER SESSION SET TIME_ZONE='-8:00';
SELECT SYSDATE, SYSTIMESTAMP, CURRENT_DATE,
CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;
DATE
DD-MON-RR
TIMESTAMP([N])
DD-MON-RR HH24:MI:SS.SSSSSS
N MEANS NUMBER OF MILLI SECONDS(OPTIONAL), can give between
0-9 and when we
dont give then by default it will take 6.
TIMESTAMP(N) WITH TIME ZONE
DD-MON-RR HH24:MI:SS.SSSSSS AM/PM TZH:TZM/TIME_ZONE_REGION
TIMESTAMP(N) WITH LOCAL TIME ZONE
DD-MON-RR HH24:MI:SS.SSSSSS
DROP TABLE DATE_DEMO;
CREATE TABLE date_demo(d1 DATE, d2 TIMESTAMP(3), d3
TIMESTAMP WITH TIME ZONE,
d4 TIMESTAMP WITH LOCAL TIME ZONE);
DESC DATE_DEMO;
select sysdate from dual;
INSERT INTO date_demo VALUES(SYSDATE, SYSDATE, SYSDATE,
SYSDATE);
SELECT * FROM date_demo;
ALTER SESSION SET TIME_ZONE='+5:30';
SELECT * FROM date_demo;
ALTER SESSION SET TIME_ZONE='-10:00';
SELECT * FROM DATE_DEMO;
ALTER SESSION SET TIME_ZONE=LOCAL;
Arithmetic with Dates
=====================
SELECT SYSDATE FROM DUAL;
SELECT SYSDATE + 1 FROM DUAL;
SELECT SYSDATE - 4 FROM DUAL;
SELECT SYSDATE / 4 FROM DUAL;
SELECT SYSDATE * 4 FROM DUAL;
SELECT sysdate - TO_DATE('04-SEP-13') FROM dual;
SELECT ename, SYSDATE - HIREDATE FROM EMP;
SELECT HIREDATE - SYSDATE FROM EMP;
SELECT sysdate + to_date('04-SEP-13') FROM dual;
SELECT sysdate + hiredate FROM emp;
Note: We can add and subtract NUMBER value with DATE value
but we cannot
multiply or divide.
We can subtract one date value with another date value but
we cannot add,
cannot multiply, cannot divide.
select sal||'*' col1 from emp;
DATE Functions: 1.LAST_DAY, 2. NEXT_DAY, 3.ADD_MONTHS,
4.MONTHS_BETWEEN, 5.ROUND,
6. TRUNC
LAST_DAY function will return last date of the month.
ALTER session SET nls_date_format='DD-MON-RR';
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY('11-FEB-2013') FROM DUAL;
NEXT_DAY function will return next day's date from the week.
SELECT NEXT_DAY(SYSDATE,'FRIDAY') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'TUESDAY') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'WEDNESDAY') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,1) FROM DUAL;
SELECT NEXT_DAY(SYSDATE-4,2) FROM DUAL;
SELECT next_day(sysdate-1,'wednesday') FROM dual;
SELECT NEXT_DAY('05-MAR-14','WEDNESDAY') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,4) FROM DUAL;
SELECT * FROM emp WHERE next_day(hiredate,
'thursday')=hiredate;
select hiredate, to_char(hiredate, 'day'), hiredate-1,
to_char(hiredate-1, 'day') from emp;
SELECT * FROM emp WHERE next_day(hiredate,
'thursday')=hiredate+7;
select hiredate, to_char(hiredate, 'day'), hiredate+7,
to_char(hiredate+7, 'day') from emp;
select to_char(to_date('02-apr-81'), 'day'),
next_day('02-apr-81', 'thursday') from dual;
select * from emp next_day(hiredate, 'thursday')=
hiredate+7;
ADD_MONTHS function will return the date after adding the
months with the current date.
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,8) FROM DUAL;
SELECT hiredate, ADD_MONTHS(hiredate, 13) FROM emp;
SELECT hiredate, ADD_MONTHS(hiredate, -13) FROM emp;
MONTHS_BETWEEN function will return the interval value
between two dates,
it will return number of months.
SELECT MONTHS_BETWEEN(SYSDATE, '01-MARCH-1990') FROM dual;
SELECT months_between('31-DEC-14', '05-MAR-14') FROM dual;
SELECT months_between('31-DEC-15', '05-MAR-14')/12*365 FROM
dual;
SELECT round(MONTHS_BETWEEN(SYSDATE, '01-MARCH-1990')) FROM
dual;
SELECT round(MONTHS_BETWEEN(SYSDATE, '01-MARCH-1990'))/12
FROM dual;
SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate) job_duration
FROM emp;
SELECT ename, MONTHS_BETWEEN(hiredate, SYSDATE) job_duration
FROM emp;
SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate)/12
job_duration FROM emp;
SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate)/12
job_duration1,
ROUND(MONTHS_BETWEEN(SYSDATE, hiredate)/12) job_duration2
FROM emp;
SELECT * FROM emp;
-- Query to display employees who got hired on Tuesday
SELECT next_day(to_date('21-jan-14')-1,'tuesday') FROM dual;
SELECT ename, hiredate, TO_CHAR(hiredate,'DAY') FROM EMP
WHERE NEXT_DAY(HIREDATE,'TUESDAY')=HIREDATE+7;
SELECT * FROM emp;
SELECT ename, hiredate, TO_CHAR(hiredate,'DAY') FROM EMP
WHERE NEXT_DAY(HIREDATE-1,'TUESDAY')=HIREDATE;
SELECT sysdate FROM dual;
Note: default date format is DD-MON-RR:
ALTER session SET nls_date_format='dd-MON-rr hh24:mi:ss';
SELECT sysdate FROM dual;
SELECT ROUND(SYSDATE,'DD') FROM DUAL;
SELECT ROUND(TO_DATE('06-MAR-14 11:00:00','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT ROUND(TO_DATE('06-MAR-14 12:00:00','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT ROUND(TO_DATE('06-MAR-14 11:59:59','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT ROUND(TO_DATE('06-MAR-14 01:00:00','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT ROUND(TO_DATE('06-MAR-14 23:00:00','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT ROUND(TO_DATE('06-MAR-14 00:30:00','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT SYSDATE FROM DUAL;
SELECT TRUNC(SYSDATE,'DD') FROM DUAL;
SELECT TRUNC(TO_DATE('06-MAR-14 11:00:00','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT TRUNC(TO_DATE('06-MAR-14 12:00:00','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT TRUNC(TO_DATE('06-MAR-14 20:59:59','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT TRUNC(TO_DATE('06-MAR-14 01:00:00','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT TRUNC(TO_DATE('06-MAR-14 23:00:00','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
SELECT TRUNC(TO_DATE('06-MAR-14 00:30:00','DD-MON-RR
HH24:MI:SS'), 'DD') FROM dual;
DD -> two digit date value for the month
SELECT SYSDATE FROM DUAL;
SELECT ROUND(SYSDATE,'MM') FROM DUAL;
SELECT HIREDATE, ROUND(HIREDATE, 'MM') FROM EMP;
SELECT ROUND(TO_DATE('15-MAR-14'), 'MM') FROM DUAL;
SELECT ROUND(TO_DATE('16-MAR-14'), 'MM') FROM DUAL;
SELECT ROUND(TO_DATE('14-FEB-04'), 'MM') FROM DUAL;
SELECT ROUND(TO_DATE('16-FEB-04'), 'MM') FROM DUAL;
SELECT ROUND(TO_DATE('14-FEB-2014'), 'MM') FROM DUAL;
SELECT ROUND(TO_DATE('15-FEB-2014'), 'MM') FROM DUAL;
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;
SELECT HIREDATE, TRUNC(HIREDATE, 'MM') FROM EMP;
SELECT TRUNC(TO_DATE('28-FEB-14'), 'MM') FROM DUAL;
SELECT TRUNC(TO_DATE('02-FEB-14'), 'MM') FROM DUAL;
SELECT HIREDATE, ROUND(HIREDATE, 'MM'), TRUNC(HIREDATE,
'MM') FROM EMP;
SELECT TRUNC(SYSDATE,'MM') FROM DUAL;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RRRR';
SELECT HIREDATE, ROUND(HIREDATE, 'Q'), TRUNC(HIREDATE, 'Q')
FROM EMP;
SELECT ROUND(SYSDATE,'Q') FROM DUAL;
SELECT TRUNC(SYSDATE,'Q') FROM DUAL;
SELECT TRUNC(TO_DATE('15-MAY-2013'),'Q') FROM DUAL;
SELECT ROUND(TO_DATE('15-MAY-2013'),'Q') FROM DUAL;
SELECT ROUND(TO_DATE('16-MAY-2013'),'Q') FROM DUAL;
SELECT TRUNC(TO_DATE('15-JUN-2013'),'Q') FROM DUAL;
SELECT ROUND(TO_DATE('15-JUN-2013'),'Q') FROM DUAL;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
SELECT ROUND(TO_DATE('17-SEP-2014'),'YY') FROM DUAL;
SELECT TRUNC(TO_DATE('17-SEP-2014'),'YY') FROM DUAL;
SELECT ROUND(TO_DATE('17-JUN-2014'),'YY') FROM DUAL;
SELECT TRUNC(TO_DATE('17-JUN-2014'),'YY') FROM DUAL;
SELECT HIREDATE, ROUND(HIREDATE, 'YY'), TRUNC(HIREDATE,
'YY') FROM EMP;
SELECT SYSDATE FROM DUAL;
SELECT ROUND(SYSDATE,'W') FROM DUAL;
SELECT ROUND(TO_DATE('04-MAR-2014'), 'W') FROM DUAL;
SELECT ROUND(TO_DATE('05-MAR-2014'), 'W') FROM DUAL;
SELECT ROUND(TO_DATE('19-APR-2014'), 'W') FROM DUAL;
SELECT TRUNC(TO_DATE('04-MAR-2014'), 'W') FROM DUAL;
SELECT TRUNC(TO_DATE('05-MAR-2014'), 'W') FROM DUAL;
SELECT TRUNC(TO_DATE('19-APR-2014'), 'W') FROM DUAL;
... format mask ...
yyyy - 4 digit year
yy - 2 digit year
year- full year in words
month- full month
mon- short month
mm-2 digit month
dd - 2 digit day(date)
d - day of the week
ddd - day of the year
dy- short name of the day
day-full name of the day
th - nd, st, rd,th
Q-Quarter
w - week of month
ww - week of year
hh-hours
hh24-hours in 24hour format
hh12 - hours in 12 hour format
mi-minutes
ss-seconds
sssss-MILLI SECONDS
SELECT SYSDATE FROM DUAL;
select new_time('07-MAR-14 14:30:00','EST','PST') from dual;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
NEW_TIME function:
Description:
The NEW_TIME function returns a date in time zone1 to a date
in time zone2.
Syntax:
The syntax for the NEW_TIME function is:
NEW_TIME( date, zone1, zone2 )
Example:
SELECT NEW_TIME('21-JAN-2014 04:21:41','EST','PST') FROM
DUAL;
SELECT NEW_TIME('07-MAR-2014 11:40:41','PST','EST') FROM
DUAL;
select hiredate from emp;
select hiredate, new_time(hiredate, 'EST', 'PST') from emp;
select sysdate, new_time(sysdate, 'EST', 'PST') from dual;
Value Description
--------------------------
AST Atlantic Standard Time
ADT Atlantic Daylight Time
BST Bering Standard Time
BDT Bering Daylight Time
CST Central Standard Time
CDT Central Daylight Time
EST Eastern Standard Time
EDT Eastern Daylight Time
GMT Greenwich Mean Time
HST Alaska-Hawaii Standard Time
HDT Alaska-Hawaii Daylight Time
MST Mountain Standard Time
MDT Mountain Daylight Time
NST Newfoundland Standard Time
PST Pacific Standard Time
PDT Pacific Daylight Time
YST Yukon Standard Time
YDT Yukon Daylight Time
No comments:
Post a Comment