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.
DECLARE @date_value datetime = '2019-05-12'; SELECT DATEADD(day, 1, EOMONTH(@date_value, -1)) GO /* Result */ 2019-05-01
In another article, I have used this script to find the the week number in a month for a given date.
- About EOMONTH function at Microdoft Docs.