Date is one of the important datatype in database. It cannot be manipulated like we do numbers or characters. We need separate functions for it to handle it in a query.
Suppose we need to see / use today’s date in the query. Then we can use inbuilt keyword ‘SYSDATE’ to get today’s date. In addition to date, it also gives current time with hour, minute, second and millisecond format. If we simply specify SYSDATE, it gives only the date. If we need time, then we have to specify the format like below.
SELECT SYSDATE FROM DUAL; — displays today’s date
— Displays today’s date in specific format
SELECT TO_CHAR (SYSDATE, ‘DD/MM/YYYY’) AS todays_date FROM DUAL;
— Displays today’s date along with current time in specific format
SELECT TO_CHAR (SYSDATE, ‘DD/MM/YYYY HH:MM:SS: SSSS’) AS todays_date FROM DUAL;
— Displays today’s date along with current time in specific format
SELECT TO_CHAR (SYSDATE, ‘DD/MM/YYYY HH:MM’) AS todays_date FROM DUAL;
Suppose we need to add or subtract months from a date column. Then we can use ADD_MONTHS function to do the same. We can specify any positive or negative whole numbers to add to date. If we specify any fraction, it will consider only the whole number.
SELECT ISBN, LOAN_DATE, ADD_MONTHS (LOAN_DATE, 1) AS DUE_DATE FROM BOOK_LOAN;
SELECT ISBN, DUE_DATE, ADD_MONTHS (DUE_DATE,-1) AS LOAN_DATE – Subtracts the month
FROM BOOK_LOAN;
Suppose we have to add days to the date column, then we can simply add number of days to it like we perform arithmetic operation on digits.
SELECT ISBN, LOAN_DATE, LOAN_DATE+3 AS DUE_DATE FROM BOOK_LOAN;
