How to find the first and last day of a month with Oracle SQL Date Functions
I constantly look to improve SQL-related skills and knowledge. Having a foundational understanding of MySQL, with moderate to heavy use (and some with PostgreSQL), I have had the Oracle Database in my eye for some time.
Oracle SQL used to feel less approachable to me. I am a Linux user and have found that depending on what Distro you are running, installing Oracle SQL — the XE Edition — can be easy-peasy or it can be, meh... (At least that was my experience.)
However, these days there is the Always Free Oracle Autonomous Database hosted on OCI which I recently signed up an account for.
With access to 2 free, 20 GB Oracle Autonomous Databases, I have begun my journey to learning the Oracle SQL Database with the end goal of becoming a certified Oracle SQL Developer, which has been a goal of mine for some time now.
I recently wrote a blog post, MySQL Count Weekday Occurrences, in which I ported over some Oracle SQL and Date functionality concepts (inspired by this YouTube video) to comparable MySQL queries. In this post, I’ll cover a small subset of the Date functionality present in Oracle SQL as I understand it with the purpose of determining the first and last day of a given month. (The examples in this post target the current month as of the time of writing: April 2021).
Oracle SQL Date Functions
Below are 4 date functions I’ll use for the following example queries:
SYSDATE— Provides the current date and time of the OS (operating system) the Oracle database is running/installed on. (Note:SYSDATEaccepts no parameters.)LAST_DAY()— Returns the last day of the month for the provided date parameter.TRUNC()— Rounds down a date value to an optionally specified format model (E.g. month, year, etc…)ADD_MONTHS()— Accepts 2 parameters: a date value and a number representing months.ADD_MONTHS()returns the date value result of adding the specified number of months to the provided date parameter.
