Home > Articles > Data

  • Print
  • + Share This
From the author of

Month End

Some DBMSs have a built-in function that returns the last day of the month. For example, the Oracle function LAST_DAY(DATE '1992-04-01') returns 30. What if the DBMS does not provide such a function, though?

Again using standard SQL, we have a (somewhat complex) solution to the question: What is the last day of the month? Let's take it in steps because the initial parts have some independent value. Assume that there is a table named Table1 with a DATE column named datecol.

To get the last day of the previous month, you would use this query:

SELECT datecol – 
CAST (EXTRACT (DAY FROM datecol) AS INTERVAL DAY)
FROM Table1;

Thus, you would do this to get the first day of the current month:

SELECT datecol – 
CAST ((EXTRACT (DAY FROM datecol) - 1) AS INTERVAL DAY)
FROM Table1;

You would do this to get the first day of the following month:

SELECT datecol – 
CAST ((EXTRACT (DAY FROM datecol) - 1) AS INTERVAL DAY) + 
   INTERVAL '1' MONTH
FROM Table1;

And you would do this to get the last day of the current month:

SELECT datecol – 
CAST ((EXTRACT (DAY FROM datecol) - 1) AS INTERVAL DAY) + 
   INTERVAL '1' MONTH – 
   INTERVAL '1' DAY 
FROM Table1;

Quod erat demonstrandum.

  • + Share This
  • 🔖 Save To Your Account