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 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
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
| Element | Description |
| Date Format Elements | |
| SCC or CC | Century; S prefixes BC date with – |
| YYYY or SYYYY | Year; S prefixes BC date with – |
| YYY or YY or Y | Last 3, 2, or 1 digit of the year |
| Y,YYY | Year with a comma in this position |
| IYYY, IYY, IY, I | 4, 3, 2, or 1 digit year based on the ISO standard |
| SYEAR or YEAR | Year spelled out; S prefixes BC date with – |
| BC or AD | BC/AD indicator |
| B.C. or A.D. | BC/AC indicator with periods |
| Q | Quarter of year |
| MM | Month, two-digit value |
| MONTH | Name of month padded with blanks to length of 9 characters |
| MON | Name of month, three-letter abbreviation |
| RM | Roman numeral month |
| WW or W | Week of year or month |
| DDD or DD or D | Day of year, month or week |
| DAY | Name of day padded with blanks to length of 9 characters |
| DY | Name of the day; 3 letters abbreviation |
| J | Julian day; the number of days since 31 December 4713 BC |
| Time Format Elements | |
| AM or PM | Meridian indicator |
| A.M. or P.M. | Meridian indicator with periods |
| HH or HH12 or HH24 | Hour of day or hour(1-12) or hour(0-23) |
| MI | Minute (0-59) |
| SS | Second (0-59) |
| SSSSS | Seconds past midnight (0-86399) |
| Suffixes | |
| TH | Ordinal number (i.e. DDTH for 5TH) |
| SP | Spelled-out number (i.e. DDSP for FIVE) |
| SPTH or THSP | Spelled-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
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 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 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 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 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 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
| 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 |
| A 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) 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
