SQL DATE FUNCTIONS
1) Add_months function: By using this function we can add particular number of months to the given date.
Example: SELECT ADD_MONTHS(TO_DATE(’12-05-22′),4) FROM DUAL;
Output of the function is given below.
2) Least_Date function: By using this function we can get least date from the given two dates.
Example: SELECT LEAST (TO_DATE(‘1-10-20’),TO_DATE(‘1-1-20’))FROM DUAL;
Output of the function is given below.
3) Months_Between function: By using this function we can return number of months between two dates.
Example: SELECT MONTHS_BETWEEN (TO_DATE(‘1-10-20’),TO_DATE(‘1-1-20’))FROM DUAL;
Output of the function is given below.
4) Last_Day function: By using this function we can return last day of the particular month in the given date.
Example: SELECT LAST_DAY(TO_DATE(’12-05-20′)) FROM DUAL;
Output of the function is given below.
5) LOCALTIMESTAMP: This function returns date and time of the local time stamp.
Example: SELECT LOCALTIMESTAMP FROM DUAL;
Output of the function is given below.
6) Trunc_SysDate: This function returns first date of the month in a given date. Respectively, we can get for particular year(YYYY).
Example: SELECT TRUNC(SYSDATE,’MM’) as result1 , LAST_DAY(SYSDATE) as result2 from dual;
Output of the function is given below.
7) Sys Timestamp: This function returns Indian standard date and time.
Example: SELECT SYSTIMESTAMP FROM DUAL;
Output of the function is given below.
8) Next_Day Function: This function returns the upcoming day from the given date and day.
Example: SELECT NEXT_DAY(’20-10-22′,’Tuesday’) from dual;
Output of the function is given below.
9) Sys_Date Function: This function returns a date which is 7days from the system date.
Example: SELECT SYSDATE+7 from dual;
Output of the function is given below.
10) Next_Day Function: This function returns immediate given day from the given date.
Example: SELECT NEXT_DAY(’29-DEC-2023′, ‘MONDAY’) AS “Next monday” FROM DUAL;
Output of the function is given below.
11) Number DS_Intervel: converts minute of the time interval into decimal. It can be a min,hour,second or milliseconds.
Example: SELECT NUMTODSINTERVAL(2, ‘MINUTE’)FROM DUAL;
Output of the function is given below.
12)Extract Month from to date: This function extracts month from given date.
Example: SELECT extract(month from to_date(’12-12-23′)) from dual;
Output of the function is given below.
13) To_Char function: This function returns the date/month/year which ever mentioned in the syntax, along with the given date.
Example: SELECT TO_CHAR(TO_DATE(’12-5-2023′),’YYYY’)FROM DUAL;
Output of the function is given below.
14) Extract Minute from Current_Timestamp function: It extracts minute from the current timestamp.
Example: SELECT EXTRACT(MINUTE FROM CURRENT_TIMESTAMP ) FROM DUAL;
Output of the function is given below.
15) Systimestamp function: It returns the system timestamp in the customized date format.
Example: SELECT to_char(SYSTIMESTAMP,’YYYY-MM-DD’) from DUAL;
Output of the function is given below.
