DATE FUNCTION IN SQL

•The default date format is DD-MM-YY.
•The SYSDATE function does not take any arguments. It returns system’s current date.
•Oracle also has a dummy table called DUAL. It is owned by used SYS and it is available to all users.
•SELECT SYSDATE
FROM DUAL;
•The query will display the current date.
•The DUAL table can be used to display the outcome of any character or number function


Date Arithmetic
•Date + number Add a number to days to a date.
•Date – number Subtract a number of days from a date.
•Date + number/24 Adds a number of hours to a date.
•Date1 – Date2 Gives the number of days between two days.
•e.g. Age Calculation from Birth date.
• SELECT Last, First, (SYSDATE – Birthdate) / 365 AS AGE
FROM employee;
LAST FIRST AGE
-----------------------------------------------------
Sagar Mohanty 20.245132
Bijaya Das 21.152984
•We can use TRUNC ((SYSDATE – Birthdate) / 365.25) To truncate the leap year


DATE FUNCTIONS
1)MONTHS_BETWEEN (date1, date2)
–Find number of months between two dates.
–MONTHS_BETWEEN (’01-DEC-05’, ’11-JAN-05’) = 10.677
–Date1 is latter than date2, otherwise negative number will be displayed.
2)ADD_MONTHS (date, m)
–Adds calendar months to a date.
–ADD_MONTHS (’06-sep-00’, 3) = ’06-DEC-00’
–m is the number of months supplied.


3)NEXT_DAY (date, ‘day’)
–Finds next occurrence of a day from given date.
–NEXT_DAY (‘18-AUG-06’, ‘SUNDAY’) = ’20-AUG-06’
4)LAST_DAY (date)
–Returns last day of the month.
–LAST_DAY (‘18-AUG-06’) = ’31-AUG-06’


5)ROUND (date [, ‘format’])
–Rounds date to nearest day, month, or year.
–ROUND (‘18-AUG-06’, ‘MONTH’) = ’01-SEP-06’
–ROUND (‘18-AUG-06’, ‘YEAR’) = ’01-JAN-07’
6)TRUNC (date [, ‘format’])
–Truncates date to nearest day, month, or year.
–TRUNC (‘18-AUG-06’, ‘MONTH’) = ’01-AUG-06’
–TRUNC (‘18-AUG-06’, ‘YEAR’) = ’01-JAN-06’