Date / Time related queries

Get the first day of the month

Quickly returns the first day of current month. Instead of current month you want to find first day of month where a date falls, replace SYSDATE with any date column/value

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month" FROM DUAL

Get the last day of the month

This query returns last day of current month. The query automatically takes care of leap year. Also similar to above query replace SYSDATE with any other date column/value to find last day of that particular month.

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month" FROM DUAL

Get the first day of the Year

First day of year is always 1-Jan. This query can be use in stored procedure where you quickly want first day of year for some further calculation. Replace SYSDATE with any date column/value to find first day of that year.

SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL

Get the last day of the year

Similar to the above query this one returns last day of current year. Replace SYSDATE with any date column/value to find last day of that year.

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL

Get number of days in current month

This query returns number of days in current month. You can change SYSDATE with any date/value to know number of days in that month.

SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days FROM DUAL

Get number of days left in current month

Below query calculates number of days left in current month.

SELECT SYSDATE,LAST_DAY (SYSDATE) "Last",LAST_DAY (SYSDATE) - SYSDATE "Days left" FROM DUAL

Get number of days between two dates

Use this query to get difference between two dates in number of days.

SELECT ROUND ( (MONTHS_BETWEEN ('31-Mar-2014', '30-Mar-2014') * 30), 0)num_of_days  FROM DUAL

Display each months start and end date upto last month of the year

This query displays start date and end date of each month in current year. This might be used for certain types of calculations.

SELECT  ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,

        TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date

FROM XMLTABLE ('for $i in 0 to xs:int(D) return $i'

                PASSING XMLELEMENT (d,FLOOR(MONTHS_BETWEEN(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR') - 1, 12),SYSDATE)))COLUMNS i INTEGER PATH '.')

               

Get number of seconds passed since today (since 00:00 hr)

SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 "num_of_sec_since_morning" FROM DUAL

Get number of seconds left today (till 23:59:59 hr)

SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 "num_of_sec_left" FROM DUAL;

PREVIOUS POST || NEXT POST