How to work with date in Oracle sql

This post I am trying to explain many things  about oracle date datatype 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 difference in years

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

Date Type
DATE is the 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

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_MIDNIGH
———————- ——— ———————-
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 ASNI 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);

Oracle Sql date Functions

ADD_MONTHS  

Date Function Description
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 Function Description
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 Function Description
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 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 Function Description
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

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 a last day in the month, MONTHS_BETWEEN returns a fractional number. The fractional component is calculated on a 31-day month basis and also takes into account 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

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 Function Description
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 Function Description
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 lot of arithmetic operations on 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 a date by dividing the number of hours by 24.

Addition and Substraction 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

Description Date Expression
Now Sysdate
Tomorrow/ next day Sysdate +1
Seven days  ago SYSDATE -7
One hour from now SYSDATE + 1/24
Three hours from now SYSDATE + 3/24  or SYSDATE + 1/8
An half hour from now SYSDATE + 1/48
10 minutes from now SYSDATE + 10/1440
30 seconds from now SYSDATE + 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) num_months

FROM dual;

 

DIFF_IN_DAYS   DIFF_IN_MONTHS  DIFF_IN_MONTHS_NO_FRACTION  DIFF_IN_YEAR_NO_FRACTION  NUM_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 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. It is not a complete guide but I have tried to present 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