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 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ДЄrithmetic operation on date difference to find to number of days between them select * from fnd_table where trunc(creation_date) select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual 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. Always explicitly perform the conversions with the TO_CHAR, TO_DATE, and TO_TIMESTAMP functions, or use ANSI DATE or TIMESTAMP literals.ДЈ) When doing date or timestamp comparisons, always consider the impact of the time component. ,TO_CHAR(TRUNC(SYSDATE), 'MM/DD/YYYY HH:MI:SS AM') date_with_time_midnightДЇROM dual 2 3 4 DATE_WITH_TIME TODAY DATE_WITH_TIME_MIDNIGHTДў) Never rely on implicit conversions of strings to dates, or dates to strings. SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS AM') date_with_time At exactly midnight the time is 00:00:00. In order to get the system date and time returned in a TIMESTAMP datatype, you can use the SYSTIMESTAMP function such as:ДЎ)Both DATE and TIMESTAMP types always contain a date and time component. 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 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. This issue is solved with the TIMESTAMP datatype Timestamp 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. Year, century, hours, minutes, and seconds. It has the ability to store the month, day, Arithmetic With Oracle date Date Data TypeДATE is the oracle datatype that we are all familiar with when we think about representing date and time values.

Sql statement for today's date