Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

function

Oracle PL/SQL TO_CHAR Function

by

Oracle PLSQL TO_CHAR FunctionOracle TO_CHAR Function is used to convert number type or date type into string type. Oracle DATE TO_CHAR query can convert a date type to character type. SQL TO_CHAR Function can be used with SQL SYSDATE also. PLSQL TO_CHAR Function syntax and examples are shown below.

Oracle TO_CHAR Function Syntax

SELECT TO_CHAR(value/date, [FORMAT_MASK],[NLS_LANGUAGE])
FROM table_name;

In the above SQL TO_CHAR Syntax:

  • FORMAT_MASK is an optional field, if present; it is used to convert value into string.
  • NLS_LANGUAGE is also an optional field, if present; it is used to convert value into string.

Oracle TO_CHAR Function – Converting Number to String Example

Oracle TO_CHAR Function can convert Numbers to String.

Table below shows how SQL TO_CHAR Function converts numbers to string:

Oracle TO_CHAR Query
 Output 
 SELECT TO_CHAR(12345.12345,'99999.999') "PLSQL TO_CHAR"
 FROM DUAL;
 12345.123 
 SELECT TO_CHAR(12345.12345,'99,999.999') "PLSQL TO_CHAR"
FROM DUAL;
 12,345.123 
 SELECT TO_CHAR(12345.12345,'$99,999.999') "PLSQL TO_CHAR"
 FROM DUAL;
 $12,345.123 
 SELECT TO_CHAR(12345.12345,'$0099,999.999') "PLSQL TO_CHAR"
FROM DUAL;
 $0012,345.123 
 SELECT TO_CHAR(12345.12345,'99999.9') "PLSQL TO_CHAR"
FROM DUAL;
 12345.1

Note: We have aliased every query result as PLSQL TO_CHAR.

Oracle TO_CHAR Function – Oracle DATE TO_CHAR Example

Oracle TO_CHAR Function can also be used with SQL SYSDATE.

Oracle DATE TO_CHAR queries can be written to convert date type to date type.

For example, table below shows Oracle DATE TO_CHAR conversion with SYSDATE as parameter.

Parameter Explanation Oracle DATE TO_CHAR Query
 Output
YEAR Year, spelled out in words 
 SELECT TO_CHAR(SYSDATE,'YEAR')
FROM DUAL;
 TWENTY TWELVE
YYYY Year in 4 digits 
 SELECT TO_CHAR(SYSDATE,'YYYY')
FROM DUAL;
 2012
YYY Year in 3 digits 
 SELECT TO_CHAR(SYSDATE,'YYY')
FROM DUAL;
 012
YY Year in 2 digits 
 SELECT TO_CHAR(SYSDATE,'YY')
FROM DUAL;
 12
Y Year in 1 digit 
 SELECT TO_CHAR(SYSDATE,'Y')
FROM DUAL;
 2
IYYY Year in 4 digits based on ISO standard 
 SELECT TO_CHAR(SYSDATE,'IYYY')
FROM DUAL;
 2012
IYY Last 3 digits of year based on ISO standard 
 SELECT TO_CHAR(SYSDATE,'IYY')
FROM DUAL;
 012
IY Last 2 digits of year based on ISO standard 
 SELECT TO_CHAR(SYSDATE,'IY')
FROM DUAL;
 12
I Last 2 digit of year based on ISO standard 
 SELECT TO_CHAR(SYSDATE,'I')
FROM DUAL;
 2
Q Quarter of Year TO_CHAR(1,2,3,4) January-March 1st quarter 
 SELECT TO_CHAR(SYSDATE,'Q')
FROM DUAL;
 4
MM Month TO_CHAR(01-12) January=01 
 SELECT TO_CHAR(SYSDATE,'MM')
FROM DUAL;
 11
MON Short name for month e.g. NOV 
 SELECT TO_CHAR(SYSDATE,'MON')
FROM DUAL;
 NOV
MONTH Full name of month e.g. JUNE 
 SELECT TO_CHAR(SYSDATE,'MONTH')
FROM DUAL;
 NOVEMBER
RM Roman Numeral for month Jan = I, Dec = XII 
 SELECT TO_CHAR(SYSDATE,'RM')
FROM DUAL;
 XI
WW Week of Year, 01 Jan – 07 Jan 1st week 
 SELECT TO_CHAR(SYSDATE,'WW')
FROM DUAL;
 44
W Week of month (1-5) starts at 1st – 7th of month is first week 
 SELECT TO_CHAR(SYSDATE,'W')
FROM DUAL;
 1
IW Week of Year (1-52) or(1-53) based on ISO standard 
 SELECT TO_CHAR(SYSDATE,'IW')
FROM DUAL;
 44
D Day of Week (1-7) 
 SELECT TO_CHAR(SYSDATE,'D')
FROM DUAL;
 5
DD Day of Month(1-31) 
 SELECT TO_CHAR(SYSDATE,'DD')
FROM DUAL;
 01
DDD Day of Year (1-366) 
 SELECT TO_CHAR(SYSDATE,'DDD')
FROM DUAL;
 306
DAY Name of Day e.g. MONDAY 
 SELECT TO_CHAR(SYSDATE,'DAY')
FROM DUAL;
 THURSDAY
DY Short name of Day e.g. MON 
 SELECT TO_CHAR(SYSDATE,'DY')
FROM DUAL;
 THU
J Julian day; the number of days since January 1, 4712 BC. 
 SELECT TO_CHAR(SYSDATE,'J')
FROM DUAL;
 2456233
HH Hour of Day (1-12) 
 SELECT TO_CHAR(SYSDATE,'HH')
FROM DUAL;
 05
HH12 Hour of Day (1-12) 
 SELECT TO_CHAR(SYSDATE,'HH12')
FROM DUAL;
 05
HH24 Hour of Day (0-23) 
 SELECT TO_CHAR(SYSDATE,'HH24')
FROM DUAL;
 17
MI Minute (0-59) 
 SELECT TO_CHAR(SYSDATE,'MI')
FROM DUAL;
 59
SS Seconds (0-59) 
 SELECT TO_CHAR(SYSDATE,'SS')
FROM DUAL;
 41
SSSS Seconds past midnight (0-86399) 
 SELECT TO_CHAR(SYSDATE,'SSSS')
FROM DUAL;
 0000

Oracle SYSTIMESTAMP Function

by

SQL SYSTIMESTAMP FunctionOracle SYSTIMESTAMP Function returns the current date and time along with fractional seconds and time zone as in the local database.

Oracle SYSTIMESTAMP Function Syntax

SELECT SYSTIMESTAMP
FROM table_name;

Oracle SYSTIMESTAMP – Using SQL SELECT Statement Example

Oracle SYSTIMESTAMP is used with the SQL SELECT Statement.
For example, below SYSTIMESTAMP query returns current date/time with fractional seconds/time zone as per local database.

SELECT SYSTIMESTAMP
FROM dual;

The above query may return the current date and time along with the fractional seconds and time zone from the local database e.g. “11/1/2012 4:51:35.428000 PM +05:30”.

The output may differ based on the local database time and time zone settings.

Oracle SYSTIMESTAMP – Using SQL TRUNC Function Example

Oracle SYSTIMESTAMP can be used with the SQL TRUNC Function to view the date component only.

For example, below Oracle SYSTIMESTAMP query returns current date as per local database.

SELECT TRUNC(SYSTIMESTAMP)
FROM dual;

The above query may return the current date and time along with the fractional seconds and time zone from the local database e.g. “11/1/2012”.

The output may differ based on the local database date settings.

Oracle SYSTIMESTAMP – Using + and – Example

Oracle SYSTIMESTAMP can be used with the + and – signs to get forward and previous dates.

For example, below Oracle SYSTIMESTAMP query returns systimestamp if next day.

SELECT (SYSTIMESTAMP+1)
FROM dual;

The above query may return the current date and time along with the fractional seconds and time zone from the local database e.g. “12/1/2012”.

The output may differ based on the local database date settings.

Oracle SYSDATE Function

by

Oracle SYSDATE FormatOracle SYSDATE is a pseudo column which returns the date and time as per database. Oracle SYSDATE Format for time and date depends on the value of NLS_DATE_FORMAT parameter. Oracle SQL SYSDATE Function does not require any parameter.Also, Oracle TRUNC SYSDATE query can be written to remove the time part from Oracle SQL SYSDATE and SYSDATE-1 Oracle query will return date of previous day.

Oracle SYSDATE Function Syntax

SELECT SYSDATE
FROM table_name;

Oracle SYSDATE – Simple Usage Example

We can use SQL SYSDATE pseudo column to fetch the date and time of the database.

For example, the SQL SYSDATE query below returns the database’s date and time

SELECT SYSDATE
FROM dual;

Above SQL SYSDATE query returns current date and time from local database e.g. “11/1/2012 4:36:50 PM”

Oracle SQL SYSDATE – Using SYSDATE-1 Oracle Example

Oracle SQL SYSDATE can be used with the + and – signs.

For example, we can use SYSDATE-1 Oracle query in SQL SELECT Statement to get the date of previous day.

Usage of SYSDATE-1 Oracle query can be seen below”

SELECT SYSDATE-1 "SYSDATE-1 Oracle" 
FROM dual;

Above SYSDATE-1 Oracle query returns the date and time of previous day.

As we have written SYSDATE-1 Oracle query, we can also write SYSDATE-2, SYSDATE-3, SYSDATE+1, SYSDATE+2 and so on.

Note: We have aliased SYSDATE-1 as SYSDATE-1 Oracle.

Oracle SQL SYSDATE – Oracle TRUNC SYSDATE Example

Oracle TRUNC SYSDATE query allows to view only those parts of SYSDATE which we wish to view.

For example, below Oracle TRUNC SYSDATE query returns the date part and not the time part from Oracle SQL SYSDATE Function

SELECT TRUNC(SYSDATE) "Oracle TRUNC SYSDATE"
FROM dual;

Above Oracle TRUNC SYSDATE query returns the system date and not the time e.g. “11/1/2012”.

Note: We have aliased TRUNC(SYSDATE) as Oracle TRUNC SYSDATE.

Oracle/SQL MEDIAN Function

by

Oracle MEDIAN FunctionSQL Median Function returns the median of available values. Oracle Median Function accepts column and formula as parameter for median calculation.

SQL MEDIAN Function Syntax

SELECT MEDIAN(numeric column / formula)
FROM table_name
WHERE conditions;

SQL MEDIAN Function Examples

Suppose we have a table named “Employee” with the data as shown below.

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

We will see the usage of Oracle MEDIAN Function below.

SQL MEDIAN Function – Simple Usage

The simplest use of Oracle MEDIAN Function would be to calculate MEDIAN of a column.

For example: Oracle MEDIAN query returns the median of salaries from employee table.

SELECT MEDIAN(salary) Median_Salary
FROM employee;

Above mentioned Oracle MEDIAN query returned ‘28000’ as median of salaries.

Note: We have aliased MEDIAN(Salary) as Median_Salary.

SQL MEDIAN Function – Using Formula Example

Oracle MEDIAN Function also accepts formula as parameter.

For example: Oracle MEDIAN query returns median of salaries*(commission/100) from employee table as shown below

SELECT MEDIAN(salary*(commission/100)) Median_Salary_Comm
FROM employee;

Above mentioned SQL MEDIAN query returned ‘3600’ as median of salary*(commission/100).

Note: We have aliased MEDIAN(salary*(commission/100)) as Median_Salary_Comm

SQL STDDEV/Standard Deviation Function

by

SQL STDDEV FunctionSQL Standard Deviation or SQL STDDEV Function returns the standard deviation of available records. Oracle Standard Deviation or Oracle STDDEV Function can accept a column name or formula as parameter for Standard Deviation calculation.

SQL Standard Deviation Function Syntax

SELECT STDDEV(numeric column / formula)
FROM table_name
WHERE conditions;

SQL Standard Deviation Function Examples

Suppose we have a table named “Employee” with the data as shown below.

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Oracle STDDEV Function application is shown below.

SQL Standard Deviation – SQL STDDEV Simple Usage Example

SQL Standard Deviation query below will return the standard deviation of salaries of all employees.

SELECT STDDEV(salary) "SQL STDDEV"
FROM employee;

Above Oracle Standard Deviation query returns ‘9055.38513813742’ as standard deviation of all salaries in ‘employee’ table.

Note: We have aliased STDDEV(salary) as SQL STDDEV.

Oracle Standard Deviation – Oracle STDDEV Using Formula Example

SQL Standard Deviation query below calculates the Standard Deviation of Salary*Commission for ’employee’ table.

SELECT STDDEV(salary*(commission/100)) "Oracle STDDEV"
FROM employee;

Above Oracle Standard Deviation query returns ‘1913.98362932741’ as standard deviation of all salary*(commission/100) of ‘employee’ table.

Note: We have aliased STDDEV(salary*(commission/100)) as Oracle STDDEV.