Tech Honey

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

Oracle NEW_TIME Function

by

Oracle NEW_TIME FunctionOracle NEW_TIME Function is used to return the date in one time zone to a date in another time zone.

Oracle NEW_TIME Function Syntax

SELECT NEW_TIME(date,time_zone1, time_zone2)
FROM table_name;

Here in the above Oracle NEW_TIME Function Syntax:

  • ‘date’ is the date that has to be seen in another time zone.
  • ‘time_zone1’ is the time zone from where the passed date is to be converted
  • ‘time_zone2’ is the time zone to which the passed date is to be converted

Time_zone1 and time_zone2 can take the following values:

Value Explanation
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

Oracle NEW_TIME – Converting GMT to PST Example

Oracle NEW_TIME Function can convert the time from one time zone to another.

For example, the Oracle NEW_TIME Function query below converts the GMT time to PST.

SELECT NEW_TIME(SYSDATE,'GMT','PST')
FROM DUAL;

Will return something like “11/5/2012 4:24:04 AM”, here we have converted the sysdate time from GMT to PST time zone.

Oracle NEW_TIME Function – Use with SQL TO_DATE Example

Oracle NEW_TIME Function can be used with the SQL TO_DATE Function.

For example, the Oracle NEW_TIME Function query below uses SQL TO_DATE Function to convert String to Date in GMT and then returns PST.

SELECT NEW_TIME(TO_DATE('01-JAN-2012 02:30','DD-MON-YYYY HH24:MI'),PST,GMT)
FROM DUAL;

Will return something like “1/1/2012 10:30:00 AM” because we have converted the Pacific Standard Time to Greenwich Mean Time.

Oracle/SQL CURRENT_TIMESTAMP Function

by

Oracle CURRENT_TIMESTAMP FunctionOracle CURRENT_TIMESTAMP Function is used to return the current date and time of the current SQL session. SQL CURRENT_TIMESTAMP returns the TIME STAMP with the TIME ZONE value.

Oracle CURRENT_TIMESTAMP Function Syntax

SELECT CURRENT_TIMESTAMP
FROM table_name;

SQL CURRENT_TIMESTAMP Function returns the date and time as set by the SQL ALTER SESSION statement.

Oracle CURRENT_TIMESTAMP Function – Using SQL SELECT Statement Example

SQL CURRENT_TIMESTAMP Function is used with the SQL SELECT Statement.

For example, the SQL CURRENT_TIMESTAMP query below returns the current date/time with time zone of the current SQL session

SELECT CURRENT_TIMESTAMP
FROM DUAL;

May return something like “11/5/2012 12:05:49.054000 PM +05:30” depending upon the value of the SQL Session date/time settings.

Oracle CURRENT_TIMESTAMP Function – Using ALTER SESSION Example

We can alter the session time of the SQL SESSION by using the SQL ALTER SESSION Statement.

For example, the below SQL query will alter the time zone of the SQL session to “-7:00”

ALTER SESSION SET TIME_ZONE = '-7:0';

Now, the below SQL CURRENT_TIMESTAMP query returns the altered time stamp.

SELECT CURRENT_TIMESTAMP
FROM DUAL;

May return something like “11/4/2012 11:36:06.800000 PM -07:00” as the time zone has been altered.

Oracle/SQL CURRENT_DATE Function

by

SQL Current_Date FunctionIn Oracle/SQL CURRENT DATE is returned by Oracle CURRENT_DATE Function. Also, SQL CURRENT_DATE Function returns time of current SQL session.Oracle CURRENT_DATE Function returns date/time set by SQL ALTER SESSION Statement. Oracle CURRENT DATE can also be used with SQL TRUNC Function to view only the date component.

SQL CURRENT DATE Function Syntax

SELECT CURRENT_DATE 
FROM table_name;

SQL CURRENT DATE Examples

Oracle CURRENT DATE – Using SELECT Statement Example

Oracle CURRENT_DATE Function is used with SQL SELECT Statement to fetch current date/time information.

For example, below SQL query returns current date/time.

SELECT CURRENT_DATE 
FROM DUAL;

Above SQL query may return something like “11/5/2012 11:52:27 AM”.

Oracle CURRENT DATE – SET TIME ZONE Example

The ALTER SESSION SET TIME ZONE Statement alters the time zone of the database.

For example, below SQL ALTER SESSION SET TIME ZONE Statement will alter the time zone of database.

ALTER SESSION SET TIME_ZONE = '-7:0';

The SQL query below will return different data now.

SELECT CURRENT_DATE 
FROM DUAL;

Above SQL CURRENT_DATE query may return something like “11/4/2012 11:24:22 PM” because time zone has been altered.

Oracle CURRENT DATE – Removing Time Component Example

SQL TRUNC Function can be used with the SQL CURRENT_DATE to remove the time component.

For example, the SQL query below will return current date and not time.

SELECT TRUNC(CURRENT_DATE)
FROM dual;

Above SQL query may return “11/4/2012”.

Oracle/SQL MONTHS_BETWEEN Function

by

Oracle SQL MONTHS_BETWEEN FunctionOracle MONTHS_BETWEEN Function is used to return the months between two dates. SQL MONTHS_BETWEEN Function can be used with String parameters.

Oracle MONTHS_BETWEEN Function Syntax

SELECT MONTHS_BETWEEN(date1, date2)
FROM table_name;

Here in the Oracle MONTHS_BETWEEN Function Syntax

  • Date1 and date2 are the two dates passed to calculate months between these two dates.
  • If the difference between the months is in fraction, then the MONTHS_BETWEEN function calculates the fractional difference based on 31 day a month logic.
  • If date1 is ahead of date2 then a positive difference is returned else a negative difference will be returned.

Oracle MONTHS_BETWEEN Function – Using String Example

SQL MONTHS_BETWEEN Function can be used with Strings as parameters.

For example, the SQL MONTHS_BETWEEN Function query returns the months between the dates passed as string.

Note that the date1 is before date2.

SELECT MONTHS_BETWEEN('01-JAN-2012','01-MAR-2012')
FROM DUAL;

The above SQL MONTHS_BETWEEN Function query returns “-2” as date1 is before date2 and difference between months is 2.

Oracle MONTHS_BETWEEN Function – Using String Example

SQL MONTHS_BETWEEN Function query below returns the months between the dates passed as string.

Note that date2 is before date1.

SELECT MONTHS_BETWEEN('01-MAR-2012','01-JAN-2012')
FROM DUAL;

Above SQL MONTHS_BETWEEN Function query returns “2” as date1 is ahead of date2 and difference between months is 2.

Oracle MONTHS_BETWEEN Function – Using Same Dates Example

In the below SQL MONTHS_BETWEEN Function query we have passed same date as both the parameters.

SELECT MONTHS_BETWEEN('01-MAR-2012','01-MAR-2012')
FROM DUAL;

Above SQL MONTHS_BETWEEN Function query “0” because date1 is equal to date2 and difference between months becomes 0.

Oracle MONTHS_BETWEEN Function – Using dissimilar Dates Example

SQL MONTHS_BETWEEN Function returns decimal values also if the difference between the dates is not a whole number.

For example, SQL MONTHS_BETWEEN Function query below returns months between dates which are more than 1 month and less than 2 months apart.

Note date1 is ahead of date2.

SELECT MONTHS_BETWEEN('30-APR-2012','01-MAR-2012')
FROM DUAL;

Will return “1.93548387096774” as the difference between two dates.

Oracle MONTHS_BETWEEN Function – Using dissimilar Dates Example

SQL MONTHS_BETWEEN Function query below returns months between dates which are more than 1 month and less than 2 months apart.

Note date1 is before date2.

SELECT MONTHS_BETWEEN('01-MAR-2012','30-APR-2012')
FROM DUAL;

Will return “-1.93548387096774” as the difference between two dates.

TRUNC Function in Oracle SQL – PLSQL

by

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

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

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

Here in the above statement:

  • ‘date’ is the date that is passed to be truncated
  • ‘format’ is the unit up to which the rounding is to be done. If format is not given or omitted then the date is truncated to the nearest day. Any hours, minutes and seconds will be truncated off.

Considering sysdate as November 2nd 2012

Observe the table below to understand what TRUNC function does when passed with different format parameters.

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

Example 1:

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

Will return 1/1/2012

Example 2:

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

Will return 7/1/2012

Example 3:

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

Will return 8/1/2012

Example 4:

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

Will return 8/22/2012

Example 5:

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

Will return 8/19/2012