function

Oracle/SQL NEXT_DAY Function

November 2, 2012 by techhoneyadmin

Oracle 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 8^th, 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

November 2, 2012 by techhoneyadmin

Oracle 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

November 2, 2012 by techhoneyadmin

Oracle 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

November 2, 2012 by techhoneyadmin

Oracle 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”

Oracle/SQL TO_TIMESTAMP Function

November 2, 2012 by techhoneyadmin

Oracle TO_TIMESTAMP Function is used to convert string type into date type.

Oracle TO_TIMESTAMP Function Syntax

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

Here in the above Oracle TO_TIMESTAMP 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 – Using SQL TO_TIMESTAMP Query Example

SQL TO_TIMESTAMP Function is used with the SQL SELECT Statement.

For example, the SQL query below will return the date type.

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

May return “11/1/2012 7:15:31.123400000 AM”

Oracle TO_TIMESTAMP – Using SQL TRUNC Function Example

SQL TO_TIMESTAMP Function can be used with the SQL TRUNC Function.

For example, the SQL query with TRUNC Function below will return the date type and remove all the other components.

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

May return “11/1/2012”

Oracle TO_TIMESTAMP – Using + and – Signs Example

SQL TO_TIMESTAMP Function can be used with the + and – signs.

For example, the SQL query with + sign below will return the next day’s date.

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

May return “11/2/2012 7:15:31.123400000 AM”