In my previous articles i have given multiple queries which will clearly gives us knowledge about SQL. I have given multiple solutions about query to find second highest salary in SQL. In this article i would like to give answer of another interview question – What is query to find first day and last day of month? in Oracle and other database management systems.
How to find first day and last day of current month?
In this article i would like to give different queries and multiple ways to find out first day and last day of month. There are so many times in real life situations we require this query.
Oracle way :
We can find out the first day and last day using following query in oracle.
Select trim(to_date(last_day(sysdate),’DD-MM-YYYY’)) as Last_day_of_Month from Dual;
Output :
Last_day_of_month
31-08-2020
The above query is used to find out the last day of the current month.
Different oracle functions used in this query :
trim function : Which will trip the date to specified format.
to_date function : This function will convert the date in to date format.
last_day : The last day is nothing but last day of the month.
Now we can check for first day of month ,
Select trunc((sysdate),’month’) as First_day_of_month from dual;
First_day_of_month
01-08-2020
The above query will give the first day of the month in oracle.
You can use following query also to find out first day of the month,
Select trunc(last_day(sysdate)-1,’mm’) as First_day from dual;
First_day
01-08-2020 00:00:00
MS SQL Server :
We can also calculate the first day and last day of current month in SQL server database.
Query for first day of the month :
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
Last day of the month :
SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))
The above query will give us information about first day and last day of the month.
Postgresql query to find first_day and Last day of month :
We can find out first_day and last_Day of current month using following queries in postgresql,
Select date_trunc(‘month’,CURRENT_DATE);
The above query will give us first day of the month ; The last day of the month is quite difficult,
Select date_trunc(‘month’,CURRENT_DATE) + interval ‘1 month – 1 day’ ;
MySQL :
Following Mysql query is useful to calculate last_day of month,
SELECT LAST_DAY(‘2020-04-05’);
Following query is useful to calculate first_day of month,
SELECT DATE_ADD(DATE_ADD(LAST_DAY(‘2020-08-14’), INTERVAL 1 DAY), INTERVAL – 1 MONTH) AS first_day;
I hope you like this article. If you find this article useful or if you have any concerns with the same kindly comment in comments section.