How To Find The First Day Of The Month In SQL Server?

There is no straightforward way or built-in function to get the first day of the month for a specific date or current date in SQL Server. To get it, we have to write our own script. I have seen people converting the datetime to varchar and do some manipulation to get the result. Here, I have given a simple script without using varchar conversion. So, the result of the script will be in smalldatetime format.

In this script, I have used EOMONTH function to find the last day of previous month and then using DATEADD function to add a day to get the first day of the month for the given date.

Script

DECLARE @date_value datetime = '2019-05-12';

SELECT DATEADD(day, 1, EOMONTH(@date_value, -1))

GO

/* Result */
2019-05-01
Find The First Day Of The Month

In another article, I have used this script to find the the week number in a month for a given date.

Reference

Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.