In my previous articles I have given the basic sql queries with real life examples.In this article I would like to give you SQL Date Functions with real life industry examples. I will explain the different SQL Queries with date functions so that user will get idea about it.These SQL date functions examples are really very important for industry.
Example 1 : First day of Current Week, i.e. Sunday?
SELECT TO_CHAR (TRUNC (SYSDATE, ‘DAY’), ‘D’) AS DAY_IN_NO,
TO_CHAR (TRUNC (SYSDATE,’DAY’), ‘DAY’) AS DAY_IN_WORDS,
TRUNC (SYSDATE, ‘DAY’) AS DAY_DATE FROM DUAL;
Example 2 : First day of Next Week, i.e. Sunday?
SELECT TO_CHAR (TRUNC (SYSDATE, ‘DAY’), ‘D’) AS DAY_IN_NO,
TO_CHAR (TRUNC (SYSDATE,’DAY’), ‘DAY’) AS DAY_IN_WORDS,
TRUNC (SYSDATE+7, ‘DAY’) AS DAY_DATE FROM DUAL;
Example 3 : First day of Previous Week, i.e. Sunday?
SELECT TO_CHAR (TRUNC (SYSDATE, ‘DAY’), ‘D’) AS DAY_IN_NO,
TO_CHAR (TRUNC (SYSDATE,’DAY’), ‘DAY’) AS DAY_IN_WORDS,
TRUNC (SYSDATE-7, ‘DAY’) AS DAY_DATE FROM DUAL;
Example 4 : First day of Current Month?
SELECT TRUNC (SYSDATE,’MM’) FROM DUAL;
Example 5 : First day of Previous Month?
SELECT TRUNC (TRUNC (SYSDATE,’MM’)-1, ‘MM’) FROM DUAL;
SELECT TRUNC (ADD_MONTHS (SYSDATE,-1),’MM’) FROM DUAL;
Example 6 : First day of Next Month?
SELECT TRUNC (LAST_DAY (SYSDATE) +1, ‘MM’) FROM DUAL;
SELECT TRUNC (ADD_MONTHS (SYSDATE, +1),’MM’) FROM DUAL;
Example 7 : First day of Current Year?
SELECT TRUNC (SYSDATE,’YY’) FROM DUAL;
Example 8 : First day of Previous Year?
SELECT TRUNC (TRUNC (SYSDATE,’YY’)-1, ‘YY’) FROM DUAL;
Example 9 : First day of Next Year?
SELECT TRUNC (ADD_MONTHS (SYSDATE, 12),’YY’) FROM DUAL;
SELECT ADD_MONTHS (TRUNC (SYSDATE, ‘YY’), 12) FROM DUAL;
Example 9 : First Day of Current Quarter?
SELECT TRUNC (SYSDATE, ‘Q’) FROM DUAL;
Example 10 : First Day of Previous Quarter?
SELECT TRUNC (ADD_MONTHS (SYSDATE, -3), ‘Q’) FROM DUAL;
SELECT ADD_MONTHS (TRUNC (SYSDATE, ‘Q’),-3) FROM DUAL;
Example 11 : First Day of Next Quarter?
SELECT TRUNC (ADD_MONTHS (SYSDATE, 3), ‘Q’) FROM DUAL;
SELECT ADD_MONTHS (TRUNC (SYSDATE, ‘Q’), 3) FROM DUAL;
Example 12 : Last day of Current Week, i.e. Saturday?
SELECT TRUNC (SYSDATE, ‘DAY’) +6 FROM DUAL;
Example 13 : Last day of Next Week?
SELECT TRUNC (SYSDATE+7, ‘DAY’) +6 FROM DUAL;
Example 14 : Last day of Previous Week?
SELECT TRUNC (SYSDATE-7, ‘DAY’) +6 FROM DUAL;
Example 15 : Last day of Current Month?
SELECT LAST_DAY (TRUNC (SYSDATE, ‘MM’)) FROM DUAL;
SELECT TRUNC (ADD_MONTHS (SYSDATE, 1), ‘MM’)-1 FROM DUAL;
Example 16 : Last day of Previous Month?
SELECT TRUNC (SYSDATE, ‘MM’)-1 FROM DUAL;
SELECT LAST_DAY (TRUNC (SYSDATE, ‘MM’)-1) FROM DUAL;
Example 17 : Last day of Next Month?
SELECT LAST_DAY (TRUNC (ADD_MONTHS (SYSDATE, 1),’MM’)) FROM DUAL;
SELECT LAST_DAY (TRUNC (LAST_DAY (SYSDATE)+1, ‘MM’)) FROM DUAL;
Example 18 : Last day of Current Year?
SELECT TRUNC (ADD_MONTHS (SYSDATE, 12),’YY’)-1 FROM DUAL;
SELECT LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE, ‘YY’), 11)) FROM DUAL;
Example 19 : Last day of Previous Year?
SELECT TRUNC (SYSDATE, ‘YY’)-1 FROM DUAL;
SELECT ADD_MONTHS (TRUNC (ADD_MONTHS (SYSDATE,-12),’YY’), 12)-1 FROM DUAL;
Example 20 : Last day of Next Year?
SELECT ADD_MONTHS (TRUNC (ADD_MONTHS (SYSDATE, 12), ‘YY’), 12)-1 FROM DUAL;
Example 21 : Last Day of Current Quarter?
SELECT ADD_MONTHS (TRUNC (SYSDATE,’Q’), 3)-1 FROM DUAL;
SELECT LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE, ‘Q’), 2)) FROM DUAL;
Example 22 : Last Day of Previous Quarter?
SELECT TRUNC (SYSDATE, ‘Q’)-1 FROM DUAL;
Example 23 : Last Day of Next Quarter?
SELECT ADD_MONTHS (ADD_MONTHS (TRUNC (SYSDATE, ‘Q’), 3), 3)-1 FROM DUAL;
SELECT LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE, ‘Q’), 5)) FROM DUAL;
Example 24 : Get number of days in Current Month?
SELECT TO_CHAR (LAST_DAY (SYSDATE),’DD’) FROM DUAL;
SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), ‘DD’) AS INT) AS NOS_OF_DAYS_IN_MNTH FROM DUAL;
Example 25 : Get number of days left in Current Month?
SELECT TO_CHAR (LAST_DAY (SYSDATE),’DD’) AS LAST_DAY,
TO_CHAR (SYSDATE,’DD’) AS CURRENT_DAY, TO_CHAR (LAST_DAY (SYSDATE),’DD’) – TO_CHAR (SYSDATE,’DD’) AS DAYS_DIFF
FROM DUAL;
OR
SELECT LAST_DAY (SYSDATE) AS LAST_DAY, SYSDATE,
LAST_DAY (SYSDATE) – SYSDATE AS DAYS_LEFT
FROM DUAL;
Example 26 : Get number of days between two dates?
SELECT TO_DATE (’31-JAN-2016′,’DD-MON-YYYY’) – TO_DATE (‘1-JAN-2016′,’DD-MON-YYYY’) AS DAYS_BETWEEN_TWO_DATES FROM DUAL;
OR
SELECT ROUND (MONTHS_BETWEEN (’31-JAN-2016′, ’01-JAN-2016′)*30, 0) DAYS_BETWEEN_TWO_DATES FROM DUAL;
OR
SELECT CEIL (MONTHS_BETWEEN (’31-JAN-2016′, ’01-JAN-2016′)*30) DAYS_BETWEEN_TWO_DATES FROM DUAL;
OR
SELECT TRUNC (SYSDATE) – TO_DATE (‘1-MAY-2016′,’DD-MON-YYYY’) AS DAYS_BETWEEN_TWO_DATES FROM DUAL;
Example 27 : Display each month Start and End date upto last month of the year?
SELECT ALL_MONTHS AS START_DATE_MONTH,
TO_CHAR (LAST_DAY (ALL_MONTHS),’DD-MON-YY’) AS END_DATE_MONTH
FROM
(SELECT TO_CHAR (ADD_MONTHS (’01-JAN-2016′, LEVEL-1),’DD-MON-YY’) AS ALL_MONTHS FROM DUAL CONNECT BY LEVEL <= 12);
Example 28 : Get number of seconds passed since today (since 00:00 hr.)?
SELECT (SYSDATE – TRUNC (SYSDATE)) * 24 * 60 * 60 NOS_OF_SECS_FROM_MORNING FROM DUAL;
Example 29 : Get number of seconds left today (till 23:59:59 hr.)?
SELECT (TRUNC (SYSDATE+1) – SYSDATE) *24 *60 *60 AS NOS_OF_SECS_LEFT FROM DUAL;
These are some most important SQL Date Functions Examples.I hope you will like the SQL Date Functions Examples article.If you like this article or if you have some concerns with the same kindly comment in comments section.