Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.