Tech Honey

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

ROUND Function in Oracle SQL – PLSQL

by

The ROUND function in Oracle SQL / PLSQL is used to return the date rounded to a specific instance.

Syntax for the ROUND function in Oracle SQL / PLSQL is:

SELECT ROUND(date,[format])
FROM table_name;

Here in the above statement:

  • ‘date’ is the date that is passed to be rounded
  • ‘format’ is the unit up to which the rounding is to be done. If format is not given or omitted then the date is rounded to the nearest day.

Considering sysdate as November 2nd 2012
Observe the table below to understand what ROUND function does when passed with different format parameters:

Parameter_Passed Explanation Select_Query Output
YEAR Rounds to nearest year 
SELECT ROUND(SYSDATE,'YEAR') FROM DUAL;
 1/1/2013
YYYY Rounds to nearest year 
SELECT ROUND(SYSDATE,'YYYY') FROM DUAL;
 1/1/2013
YYY Rounds to nearest year 
SELECT ROUND(SYSDATE,'YYY') FROM DUAL;
 1/1/2013
YY Rounds to nearest year 
SELECT ROUND(SYSDATE,'YY') FROM DUAL;
 1/1/2013
Y Rounds to nearest year 
SELECT ROUND(SYSDATE,'Y') FROM DUAL;
 1/1/2013
IYYY Rounds to nearest year based on ISO standard 
SELECT ROUND(SYSDATE,'IYYY') FROM DUAL;
 12/31/2012
IYY Rounds to nearest year based on ISO standard 
SELECT ROUND(SYSDATE,'IYY') FROM DUAL;
 12/31/2012
IY Rounds to nearest year based on ISO standard 
SELECT ROUND(SYSDATE,'IY') FROM DUAL;
 12/31/2012
I Rounds to nearest year based on ISO standard 
SELECT ROUND(SYSDATE,'I') FROM DUAL;
 12/31/2012
Q Round to the nearest Quarter of Year (1,2,3,4) January-March 1st quarter 
SELECT ROUND(SYSDATE,'Q') FROM DUAL;
 10/1/2012
MM Rounds to the 16th day of Month 
SELECT ROUND(SYSDATE,'MM') FROM DUAL;
 11/1/2012
MON Rounds to the 16th day of Month 
SELECT ROUND(SYSDATE,'MON') FROM DUAL;
 11/1/2012
MONTH Rounds to the 16th day of Month 
SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;
 11/1/2012
RM Rounds to the 16th day of Month 
SELECT ROUND(SYSDATE,'RM') FROM DUAL;
 11/1/2012
WW Same day of the week as 1st day of year 
SELECT ROUND(SYSDATE,'WW') FROM DUAL;
 11/4/2012
W Same day of the week as 1st day of month 
SELECT ROUND(SYSDATE,'W') FROM DUAL;
 11/1/2012
IW Same day of the week as 1st day of ISO year 
SELECT ROUND(SYSDATE,'IW') FROM DUAL;
 11/5/2012
D Starting Day of Week 
SELECT ROUND(SYSDATE,'D') FROM DUAL;
 11/4/2012
DY Starting Day of Week 
SELECT ROUND(SYSDATE,'DY') FROM DUAL;
 11/4/2012
DAY Starting Day of Week 
SELECT ROUND(SYSDATE,'DAY') FROM DUAL;
 11/4/2012
DD Nearest Day 
SELECT ROUND(SYSDATE,'DD') FROM DUAL;
 11/3/2012
DDD Nearest Day 
SELECT ROUND(SYSDATE,'DDD') FROM DUAL;
 11/3/2012
J Nearest Day 
SELECT ROUND(SYSDATE,'J') FROM DUAL;
 11/3/2012
HH Hour of Day (1-12) 
SELECT ROUND(SYSDATE,'HH') FROM DUAL;
 11/2/2012 4:00:00 PM
HH12 Hour of Day (1-12) 
SELECT ROUND(SYSDATE,'HH12') FROM DUAL;
 11/2/2012 4:00:00 PM
HH24 Hour of Day (0-23) 
SELECT ROUND(SYSDATE,'HH24') FROM DUAL;
 11/2/2012 4:00:00 PM
MI Minute (0-59) 
SELECT ROUND(SYSDATE,'MI') FROM DUAL;
 11/2/2012 4:12:00 PM

Example1:

SELECT ROUND(TO_DATE ('22-AUG-12'),'YEAR')
FROM DUAL;

Will return 1/1/2013

Example 2:

SELECT ROUND(TO_DATE ('22-AUG-12'),'Q')
FROM DUAL;

Will return 10/1/2012

Example 3:

SELECT ROUND(TO_DATE ('22-AUG-12'),'MONTH')
FROM DUAL;

Will return 9/1/2012

Example 4:

SELECT ROUND(TO_DATE ('22-AUG-12'),'DDD')
FROM DUAL;

Will return 8/22/2012

Example 5:

SELECT ROUND(TO_DATE ('22-AUG-12'),'DAY')
FROM DUAL;

Will return 8/19/2012

Oracle/SQL NEXT_DAY Function

by

Oracle SQL NEXT_DAY FunctionOracle NEXT_DAY Function is used to return the date of the next occurrence of the weekday from the date passed. SQL NEXT_DAY Function can be used with SQL SYSDATE,with other data types and SQL TO_CHAR Function.

Oracle NEXT_DAY Function Syntax

SELECT NEXT_DAY(date,weekday)
FROM table_name;

Here in the above Oracle NEXT_DAY Syntax

  • ‘date’ is the date that is passed to the NEXT_DAY function
  • ‘weekday’ is any day of the week (e.g. Monday, Tuesday, Wednesday, Thursday, Friday, Saturday or Sunday)

Oracle NEXT_DAY Function – Using SQL SYSDATE Example

Oracle NEXT_DAY Function can be used with SQL SYSDATE.

For example, the SQL NEXT_DAY Function query below returns the next occurrence of MONDAY from SYSDATE.

SELECT NEXT_DAY(SYSDATE,'MONDAY')
FROM DUAL;

Will return the date for next MONDAY e.g. “11/5/2012 2:40:59 PM”.
The next day’s date returned will depend upon the SYSDATE value of date.

Oracle NEXT_DAY Function – Using VARCHAR Example

Oracle NEXT_DAY Function can be used with other data types also.

For example, the SQL NEXT_DAY Function query below returns the nearest SUNDAY ahead from ’01-Jan-2012′

SELECT NEXT_DAY('01-Jan-2012','SUNDAY')
FROM DUAL;

Will return “1/8/2012” because the next SUNDAY from ’01-Jan-2012′ is January 8th, 2012.

Oracle NEXT_DAY Function – Using SQL TO_CHAR Example

Oracle NEXT_DAY Function can also be used with the SQL TO_CHAR Function to format the output.

For example, the SQL NEXT_DAY Function query below uses SQL TO_CHAR Function to format the output.

SELECT TO_CHAR(NEXT_DAY('01-Jan-2012','SUNDAY'),'DD-MON-YYYY')
FROM DUAL;

Will return “08-JAN-2012”.

Note the format of the output is different in example 2 and example 3.

Oracle/SQL LAST_DAY Function

by

Oracle LAST_DAY FunctionOracle LAST_DAY Function is used to return the last day of the month of the date passed.SQL LAST_DAY Function can be used with SQL SYSDATE. Also, Oracle LAST_DAY Function can be used with the SQL TRUNC Function to view the date component only if we have used SYSDATE as parameter.

Oracle LAST_DAY Function Syntax

SELECT LAST_DAY(date)
FROM table_name;

Oracle LAST_DAY – Using SQL SYSDATE Example

Oracle LAST_DAY can be used with the SQL SYSDATE pseudo column.

For example, the SQL query below returns the last day of of the month as per SYSDATE.

SELECT LAST_DAY(SYSDATE)
FROM DUAL;

May return something like “11/30/2012 2:31:34 PM” depending upon the system date.

Oracle LAST_DAY- Using String Example

SQL LAST_DAY can be used with data types other than Date.

For example, the below SQL query below returns the last day of the month passed as String.

SELECT LAST_DAY('01-Jan-2012') 
FROM DUAL;

Will return “1/31/2012”.

Oracle LAST_DAY – Using SQL TO_CHAR Function Example

SQL LAST_DAY can be used with the SQL TO_CHAR Function to format the output.

For example, the SQL query below returns the last day of the month with the month spelled completely.

SELECT TO_CHAR(LAST_DAY('01-Jan-2012'),'DD-MONTH-YYYY') 
FROM DUAL;

Will return “31-JANUARY-2012”

Oracle LAST_DAY – Using SQL SYSDATE and TRUNC Function Example

SQL LAST_DAY Function can be used with the SQL SYSDATE to return the Date and time components.

If we wish to view only Date component of last day of the month then we can use SQL TRUNC Function with SQL LAST_DAY.

For example, the SQL LAST_DAY query below returns the last day of the month without showing the time.

SELECT TRUNC(LAST_DAY(SYSDATE)) 
FROM DUAL;

May return “1/31/2012” depending upon the SYSDATE.

Oracle PL/SQL ADD_MONTHS Function

by

Oracle SQL ADD_MONTHSOracle ADD_MONTHS Function returns Date by adding months to a specific date. Oracle SQL ADD_MONTHS syntax and examples are below.

Oracle ADD_MONTHS Function Syntax

SELECT ADD_MONTHS(date1,m)
 FROM table_name;

In the above Oracle ADD_MONTHS Function Syntax:

  • ‘date1’ is the value that will be added with the passed number of months.
  • ‘m’ is the number of months that will be added to the ‘date1’ passed.

Oracle ADD_MONTHS Example – Use with SYSDATE

PL/SQL ADD_MONTHS can be used with SQL SYSDATE pseudo column.

Oracle SQL ADD_MONTHS example below returns the Date and time of 4 months ahead.

SELECT ADD_MONTHS(SYSDATE,4)"PL SQL ADD_MONTHS"
FROM DUAL;

Above Oracle ADD_MONTHS example may return something like “2/2/2013 2:18:57 PM”.

The actual value returned will depend upon the SYSDATE value of database.

Note: We have aliased ADD_MONTHS(SYSDATE,4) as PL SQL ADD_MONTHS.

Oracle ADD_MONTHS Example – Using Other Data Types

Oracle ADD_MONTHS Function can be used with other data types.

In the Oracle SQL ADD_MONTHS example below are adding 5 months to a String type parameter.

SELECT 
ADD_MONTHS('01-Jan-2012',5) "PL SQL ADD_MONTHS"
FROM DUAL;

Above Oracle ADD_MONTHS example will return “6/1/2012”.

Note: We have aliased ADD_MONTHS(’01-Jan-2012′,5) as PL SQL ADD_MONTHS.

Oracle ADD_MONTHS Example – Using TO_CHAR

We can use SQL TO_CHAR Function with Oracle PL/SQL ADD_MONTHS Function.

For example, the Oracle ADD_MONTHS query below returns the spelled name of the 5th month from June.

SELECT TO_CHAR(ADD_MONTHS('01-Jun-2012',5),'DD-MONTH-YYYY') "PL SQL ADD_MONTHS"
FROM DUAL;

The above Oracle ADD_MONTHS query returns ’01-NOVEMBER -2012′.

Note: We have aliased TO_CHAR(ADD_MONTHS(’01-Jun-2012′,5),’DD-MONTH-YYYY’) as PL SQL ADD_MONTHS.

Oracle TO_TIMESTAMP_TZ Function

by

Oracle To_TimeStamp_Tz FunctionOracle TO_TIMESTAMP_TZ Function is used to convert string type to time stamp type along with the time zone.

Oracle TO_TIMESTAMP_TZ Function Syntax

SELECT TO_TIMESTAMP_TZ(string_value, [FORMAT_MASK],[NLS_LANGUAGE])
FROM table_name;

In the above Oracle TO_TIMESTAMP_TZ 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 time stamp.
  • NLS_LANGUAGE is also an optional field, if present; it is used to convert value into time stamp.

Oracle TO_TIMESTAMP_TZ Function – Use with SQL Select SELECT Statement Example

Oracle TO_TIMESTAMP_TZ Function is used with the SQL SELECT Statement.

For example the below SQL query converts string type to time stamp type along with the time zone.

SELECT TO_TIMESTAMP_TZ('2012-11-01 07:15:31.1234 PST', 'YYYY-MM-DD HH24:MI:SS.FF TZR')
FROM dual;

Will return “11/1/2012 7:15:31.123400000 AM -08:00”

Oracle TO_TIMESTAMP_TZ Function – Using with SQL SELECT Statement Example

The below Oracle TO_TIMESTAMP_TZ query converts string type to time stamp type along with the time zone.

SELECT TO_TIMESTAMP_TZ('2012-11-01 07:15:31.1234+5:30', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')
FROM dual;

Will return “11/1/2012 7:15:31.123400000 AM +05:30”