Tech Honey

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

function

Oracle TO_DATE Function

by

Oracle TO_DATE FunctionOracle TO_DATE function is used to convert string type into date type. Oracle SQL TO_DATE examples and syntax can be found below.

Oracle TO_DATE Function Syntax

SELECT TO_DATE (STRING_VALUE, [FORMAT_MASK],[NLS_LANGUAGE])
FROM table_name;

In the above Oracle TO_DATE Function Syntax:

  • STRING_VALUE is the value that will be converted into date format.
  • 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.

Table below shows Oracle SQL TO_DATE Function behavior with different parameters while converting string to date:

Parameter Explanation
AD or A.D AD indicator
AM, A.M., PM, or P.M. Meridian indicator
BC or B.C. BC indicator
D Day of week (1-7).
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
FF Fractional seconds.
Use a value from 1 to 9 after FF to indicate the number
of digits in the fractional seconds.
For example, ‘FF4’.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
I Last 3, 2, or 1 digit(s) of ISO year.
IW Week of year (1-52 or 1-53) based on the ISO standard.
IY Last 3, 2, or 1 digit(s) of ISO year.
IYY Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
J Julian day; the number of days since January 1, 4712 BC.
MI Minute (0-59).
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
RM Roman numeral month (I-XII; JAN = I).
RRRR Accepts a 2-digit year and returns a 4-digit year.A value between 0-49 will return a 20xx year.A value between 50-99 will return a 19xx year.
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
TZD Daylight savings information. For example, ‘PST’
TZH Time zone hour.
TZM Time zone minute.
TZR Time zone region.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
Y Last 3, 2, or 1 digit(s) of year.
YEAR Year, spelled out
YY Last 3, 2, or 1 digit(s) of year.
YYY Last 3, 2, or 1 digit(s) of year.
YYYY 4-digit year

Let’s have some Oracle TO_DATE Examples.

Note: The output of Oracle TO_DATE Examples may differ for you depending on the passed parameters.

Oracle TO_DATE Examples – Convert String to Date

Oracle TO_DATE examples below convert String to Date type.

SELECT TO_DATE('2012/01/09', 'yyyy/mm/dd') "Oracle SQL TO_DATE"
FROM dual;

Above Oracle TO_DATE formats the String to Date data type and returns a date value for January 9, 2012.

Note: We have aliased TO_DATE(‘2012/01/09’, ‘yyyy/mm/dd’) as Oracle SQL TO_DATE.

Oracle TO_DATE Examples – Oracle TO_DATE Format

Oracle TO_DATE Format example below returns Date type for spelled month passed as parameter.

SELECT TO_DATE('2012/January/09', 'yyyy/Month/dd') "Oracle SQL TO_DATE"
FROM dual;

Oracle SQL TO_DATE query returns Date value for January 9, 2012.

Note: We have aliased TO_DATE(‘2012/January/09’, ‘yyyy/mm/dd’) as Oracle SQL TO_DATE.

Oracle TO_DATE Examples – Oracle TO_DATE Format

Oracle TO_DATE Format example below returns Date type for partially spelled month passed as parameter.

SELECT TO_DATE('2012/Jan/09', 'yyyy/Mon/dd') "Oracle TO_DATE Format"
FROM dual;

Oracle SQL TO_DATE Function returns Date value for January 9, 2012.

Note: In Oracle TO_DATE examples above we have aliased TO_DATE(‘2012/Jan/09’, ‘yyyy/mm/dd’) as Oracle TO_DATE Format.

USERENV Function in Oracle SQL – PLSQL

by

Oracle SQL / PLSQL USERENV function is used to get the information about the current Oracle Session.

Oracle SQL / PLSQL syntax for the USERENV function is:

SELECT USERENV (parameter)
FROM table_name;

The parameter is the value that is passed to the USERENV function.

The possible values of parameter are shown below:

Parameter Explanation
CLIENT_INFO Returns user session information stored using the DBMS_APPLICATION_INFO package
ENTRYID Available auditing entry identifier
INSTANCE The number of the current instance
ISDBA Returns TRUE if the user has DBA privileges. Else, returns FALSE.
LANG The ISO abbreviation for the language e.g. US
LANGUAGE The language, territory, and character of the session. In the following format:
language_territory.characterset.
SESSIONID The identifier of the auditing session e,g, 24000
TERMINAL The OS identifier of the current session

Example 1 of using the Oracle SQL USERENV function 

 SELECT USERENV('SESSIONID')
FROM dual;

May return ‘24000’ or ‘23456’ or any other number

Example 2 of using the Oracle SQL USERENV function 

 SELECT USERENV('LANGUAGE')
FROM dual;

May return ‘AMERICAN_AMERICA.WE8MSWIN1252’

UID Function in Oracle SQL – PLSQL

by

Oracle SQL / PLSQL uses the UID function to get the id number of the user’s session for the user who is currently logged in.

Orace SQL / PLSQL syntax for the UID function is:

SELECT UID
FROM table_name;

Example: Using Oracle SQL UID function in SQL SELECT Statement 

 SELECT UID
FROM dual;

May return ‘5’ or ’15’ or any other number.

NULLIF Function in Oracle SQL – PLSQL

by

Oracle SQL / PLSQL used the NULLIF function to compare 2 expressions.

If the expressions are equal then the SQL NULLIF function returns NULL, else SQL NULLIF function will return the first expression.

Oracle SQL / PLSQL Syntax for the NULLIF function is:

SELECT NULLIF (expression_1, expression_2)
FROM table_name;

Here, expression_1 and expression_2 are the expressions to be compared.

Example: Using Oracle SQL /PLSQL NULLIF Function in SQL SELECT Statement

 SELECT NULLIF(1,1)
FROM dual;

Will return NULL

 SELECT NULLIF(1,2)
FROM dual;

Will return 2

 SELECT NULLIF('Tech Honey','Tech Honey')
FROM dual;

Will return NULL

 SELECT NULLIF('Tech','Honey')
FROM dual;

Will return ‘Tech’

 SELECT NULLIF(NULL,'Honey')
FROM dual;

Will return an ORA-00932 error because expression_1 cannot be literal NULL.

The example above shows use of Oracle SQL / PLSQL NULLIF Function.

NANVL Function in Oracle SQL – PLSQL

by

Oracle SQL / PLSQL NANVL function allows us to substitute a value for a floating point number such a BINARY_FLOAT or BINARY_DOUBLE, when an SQL Not A Number (NAN) value is encountered.

The Oracle SQL NANVL function is most commonly used to convert Not A Number (NAN) values to either 0 or NULL.

Oracle SQL / PLSQL Syntax for the NANVL function is:

SELECT NANVL (value_1, replace_with)
FROM table_name;

  • value_1 is the BINARY_FLOAT or BONARY_NUMBER to be tested for a Not A Number (NAN)
  • the replace_with is the value that will be returned if value_1 is NAN.

Example of using Oracle SQL NANVL function 

 SELECT NANVL(to_char(2),NULL)
FROM dual;

Will return NULL.