Home » Oracle » Oracle Sql » How to work with date Functions in Oracle sql

How to work with date Functions in Oracle sql

This post I am trying to explain many things  about date in oracle like oracle sql date functions,oracle sql date format, oracle sql date comparison, oracle date difference in  years,oracle date difference in  days, oracle date difference in  months

oracle date functions

Oracle has provided date and timestamp types for storing the date and time information in the oracle database.

Date Data Type

DATE is the oracle datatype that we are all familiar with when we think about representing date and time values. It has the ability to store the month, day,
year, century, hours, minutes, and seconds. The problem with the DATE datatype is its’ granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype

Timestamp

Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds. If you want to convert a DATE datatype to a TIMESTAMP datatype format, you can use the CAST function

SQL> SELECT CAST(last_login_date AS TIMESTAMP) "Date" FROM users;
Date
-----------------------------------------------------
20-APR-16 01.55.14.000000 PM
21-JUN-16 14.16.36.000000 AM
21-JUL-16 10.16.36.000000 AM
21-SEP-16 11.16.36.000000 AM
21-DEC-16 11.16.36.000000 AM

In order to get the system date and time returned in a TIMESTAMP datatype, you can use the SYSTIMESTAMP function such as:

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
01-SEP-19 01.02.17.158913 PM -04:00

Some Important point

1)Both DATE and TIMESTAMP types always contain a date and time component. At exactly midnight the time is 00:00:00.

SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS AM') date_with_time
,TRUNC(SYSDATE) today
,TO_CHAR(TRUNC(SYSDATE), 'MM/DD/YYYY HH:MI:SS AM') date_with_time_midnight
FROM dual 2 3 4 ;DATE_WITH_TIME TODAY DATE_WITH_TIME_MIDNIGHT
---------------------- --------- ----------------------
10/27/2016 11:01:00 AM 27-OCT-16 10/27/2016 12:00:00 AM

2) Never rely on implicit conversions of strings to dates, or dates to strings. Always explicitly perform the conversions with the TO_CHAR, TO_DATE, and TO_TIMESTAMP functions, or use ANSI DATE or TIMESTAMP literals.
3) When doing date or timestamp comparisons, always consider the impact of the time component. If you want to discount the time component from the comparison, use the TRUNC or ROUND functions to remove it from both sides of the comparison.

select * from fnd_table where trunc(creation_date) < trunc(sysdate);

4) You can display the required date format using nls_date_format at the session level

See also  What is PLSQL records in Oracle

Change date format in oracle using nls_date_format

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';
select sysdate from dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI:SS';
select sysdate from dual;

alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
select sysdate from dual;

5) Here is a good summary of the time formats we can use

ElementDescription
Date Format Elements
SCC or CCCentury; S prefixes BC date with –
YYYY or SYYYYYear; S prefixes BC date with –
YYY or YY or YLast 3, 2, or 1 digit of the year
Y,YYYYear with a comma in this position
IYYY, IYY, IY, I4, 3, 2, or 1  digit year based on the ISO standard
SYEAR or YEARYear spelled out; S prefixes BC date with –
BC or ADBC/AD indicator
B.C. or A.D.BC/AC indicator with periods
QQuarter of year
MMMonth, two-digit value
MONTHName of month padded with blanks to length of 9 characters
MONName of month, three-letter abbreviation
RMRoman numeral month
WW or WWeek of year or month
DDD or DD or DDay of year, month or week
DAYName of day padded with blanks to length of 9 characters
DYName of the day; 3 letters abbreviation
JJulian day; the number of days since 31 December 4713 BC
Time Format Elements
AM or PMMeridian indicator
A.M. or P.M.Meridian indicator with periods
HH or HH12 or HH24Hour of day or hour(1-12) or hour(0-23)
MIMinute (0-59)
SSSecond (0-59)
SSSSSSeconds past midnight (0-86399)
Suffixes
THOrdinal number (i.e. DDTH for 5TH)
SPSpelled-out number (i.e. DDSP for FIVE)
SPTH or THSPSpelled-out ordinal numbers (i.e. DDSPTH for FIFTH)
Other Formatting Elements
/ , .Punctuation is reproduced in the result
“of the”Quoted string is reproduced in the result

oracle date functions

We learned about Date and Timestamp data type in the previous section. We will see now important oracle date functions in detail and how we can use them

See also  How to check recycle bin in Oracle: Purge, enable/disable

ADD_MONTHS  

Date FunctionDescription
ADD_MONTHS (date, n)Returns a date value after adding ‘n’ months to the date ‘x’.
select ADD_MONTHS ('16-Sep-81', 3)      from dual
----------------------------
16-Dec-81

ADD_MONTHS always shifts the date by whole months. You can provide a fractional value for the month_shift parameter, but ADD_MONTHS will always round down to the whole number nearest zero, as shown in these
examples:

So

select ADD_MONTHS ('28-FEB-2005', 1.5)              from dual
-------------------------------------------
31-Mar-2005

We can use negative values also

select ADD_MONTHS ('28-FEB-2005', -1)                from dual
------------------------------
31-Jan-2005

Last_day

Date FunctionDescription
LAST_DAY (x)It is used to determine the number of days remaining in a month from the date ‘x’ specified.

The LAST_DAY function returns the date of the last day of the month for a given date. This function is useful because the number of days in a month varies throughout the year.

select LAST_DAY ('01-Jun-16')     from dual
----------------------------------------------------
30-Jun-2016

Next_day

Date FunctionDescription
NEXT_DAY (x, week_day)Returns the next date of the ‘week_day’ on or after the date ‘x’ occurs.

NEXT_DAY returns the date of the first weekday named by day that is later than the date. The return type is always DATE, regardless of the datatype of date. The argument day must be a day of the week in the date language of your session, either the full name or the abbreviation

select NEXT_DAY ('01-Jun-08', 'Wednesday')       from dual
-------------------------------------------
04-JUN-08

MONTHS_BETWEEN 

Date FunctionDescription
MONTHS_BETWEEN (x1, x2)Returns the number of months between dates x1 and x2.

The MONTHS_BETWEEN function calculates the number of months between two dates and returns that difference as a number

Rules for the calculation are

See also  duplicate target database for standby from active database

1)If date1 comes after date2, then MONTHS_BETWEEN returns a positive number.
2) If date1 comes before date2, then MONTHS_BETWEEN returns a negative number.
3) If date1 and date2 both fall on the last day of their respective months, then MONTHS_BETWEEN returns a whole number (no fractional component).
4)If date1 and date2 are in different months and at least one of the dates is not the last day in the month, MONTHS_BETWEEN returns a fractional number. The fractional component is calculated on a 31-day basis and also considers any differences in the time component of date1 and date2.

Examples

select MONTHS_BETWEEN ('29-FEB-2016', '31-MAR-20')    from dual
--------------------------------------------------------------------------------
-1

select  MONTHS_BETWEEN ('31-MAR-1995', '28-FEB-1994') from dual
-------------------------------------------------------------------------------
13

select  MONTHS_BETWEEN ('31-JAN-2006', '10-MAR-2006') from dual
----------------------------------------------------------------------------
-1.3225806

SELECT MONTHS_BETWEEN
(TO_DATE('02-02-1995','MM-DD-YYYY'),
TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
FROM DUAL;

Months
----------
1.03225806

An important point to remember

MONTHS_BETWEEN calculates the fractional component of the number of months by assuming that
each month has 31 days. Therefore, each additional day over a complete month counts for 1/31 of a month, and:
1 divided by 31 = .032258065

Round 

Date FunctionDescription
ROUND (x, date_format)Returns the date ‘x’ rounded off to the nearest century, year, month, date, hour, minute, or second as specified by the ‘date_format’.

The ROUND function rounds a date value to the nearest date as specified by a format mask. It is just like the standard numeric ROUND function, which rounds a number to the nearest number of specified precision, except that it works with dates

Examples

Select ROUND (TO_DATE ('12-MAR-2016'), 'MONTH') from dual;

01-MAR-2016

Select ROUND (TO_DATE ('17-MAR-2016'), 'MONTH') from dual;

01-APR-2016

select ROUND (TO_DATE ('01-MAR-2007'), 'YYYY') from dual;

01-JAN-2007

select ROUND (TO_DATE ('01-SEP-2007'), 'YEAR') from dual;

01-JAN-2008

Trunc

Date FunctionDescription
TRUNC (x, date_format)Returns the date ‘x’ lesser than or equal to the nearest century, year, month, date, hour, minute, or second as specified by the ‘date_format’.

Examples

Select TRUNC (TO_DATE ('12-MAR-2016'), 'MONTH') from dual;
01-MAR-2016

 Select TRUNC (TO_DATE ('17-MAR-2016'), 'MONTH') from dual;
01-MAR-2016

 select TRUNC (TO_DATE ('01-MAR-2007'), 'YYYY') from dual;
01-JAN-2007

select TRUNC (TO_DATE ('01-SEP-2007'), 'YEAR') from dual;
01-JAN-2007

Arithmetic With Oracle date

We can perform a lot of arithmetic operations on the oracle date datatype. We can add or subtract a number to or from a date for a resultant date value. we can Subtract two dates to find the number of days between those dates. we Add hours to date by dividing the number of hours by 24.

Addition and Subtraction examples

SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------

01-Jul-2016 06:32:12 01-Jul-2016 07:32:12 01-Jul-2016 06:33:12 01-Jul-2016 06:32:13

Other ways we can use it are

DescriptionDate Expression
NowSysdate
Tomorrow/ next daySysdate +1
Seven days  agoSYSDATE -7
One hour from nowSYSDATE + 1/24
Three hours from nowSYSDATE + 3/24  or SYSDATE + 1/8
A half-hour from nowSYSDATE + 1/48
10 minutes from nowSYSDATE + 10/1440
30 seconds from nowSYSDATE + 30/86400

Arithmetic operation on  date difference to find to number of days between them

We can subtract two dates to find  the days difference between  the dates

If the time part is same, then it will always whole number

SELECT TO_DATE('25-MAR-2016 11:00:00 AM', 'DD-MON-YYYY HH:MI:SS AM')

- TO_DATE('19-MAR-2016 11:00:00 AM', 'DD-MON-YYYY HH:MI:SS AM') diff_in_days
FROM   dual;

DIFF_IN_DAYS

----------

6

If the time part is not same, then it will always be having fractional components

SELECT TO_DATE('25-MAR-2016 10:00:00 AM', 'YYYYMMDD HH:MI:SS AM')

- TO_DATE('19-MAR-2016 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM') diff_in_days
FROM     dual;

DIFF_IN_DAYS

----------

5.95833333

SQL> SELECT  TO_DATE('25-MAR-2016 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM')

- TO_DATE('19-MAR-2016 10:00:00 AM', 'YYYYMMDD HH:MI:SS AM') diff_in_days
FROM   dual;

DIFF_IN_DAYS

----------

6.04166666

We can find the oracle date difference in  Months using the query

SELECT MONTHS_BETWEEN
(TO_DATE('02-02-1995','MM-DD-YYYY'),
TO_DATE('01-01-1995','MM-DD-YYYY') ) "Diff_in_Months", (TO_DATE('02-02-1995','MM-DD-YYYY')-
TO_DATE('01-01-1995','MM-DD-YYYY')  diff_in_days
FROM DUAL;Diff_in_Months      diff_in_days
----------------------------------------
1.03225806              32

We can also find the oracle date difference in  years using the query

SELECT (TO_DATE('20130525', 'YYYYMMDD') - TO_DATE('20100101', 'YYYYMMDD')) diff_in_days , MONTHS_BETWEEN(TO_DATE('20130525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD')) Diff_in_months
,TRUNC(MONTHS_BETWEEN(TO_DATE('20130525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))) Diff_in_months_no_fraction,
TRUNC(TRUNC(MONTHS_BETWEEN(TO_DATE('20130525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))) / 12) Diff_in_years_no_fraction
,MOD(TRUNC(MONTHS_BETWEEN(TO_DATE('20130525', 'YYYYMMDD'), TO_DATE('20100101', 'YYYYMMDD'))), 12) Diff_in_years_fraction_in_months
FROM dual;
DIFF_IN_DAYS   DIFF_IN_MONTHS  DIFF_IN_MONTHS_NO_FRACTION  DIFF_IN_YEAR_NO_FRACTION  DIFF_IN_YEARS_FRACTION_IN_MONTHS
____________________________________________________________________________________
1240                40.7741       40                                 3    4


Hope you like the post on oracle date datatype. I  have tried to explain various things like oracle date functions, oracle sql date format, oracle sql date comparison, oracle date difference in years, oracle date difference in days, oracle date difference in months. It is not a complete guide but I have tried to present a lot of useful information for the oracle sql developer

Related articles

how to write sql queries
Oracle sql tutorial :Basic Sql statement
Oracle sql tutorial :Restricting the data set
Single row functions in sql
Oracle sql decode processing
Download Oracle SQL developer
https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm#OLADM780

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top