How-To Get First Day of the Month Using T-SQL (Transact-SQL)
This function gets the first day of the month. I use it a lot in my Select and Where clauses. I also use it to set parameters or variables. It is just one of those really useful functions that saves a lot of time.
If I use this date as an example : 09/24/2010 and assuming @CurrentDate equals this date, this is how the code will get the desired results.
First I get the previous day from the current date: DATEPART(dd,@CurrentDate)-1).
This will return 23 which is the day before.
Next I substract this day from the @CurrentDate value using this code in italics: dateadd(dd,-(DATEPART(dd,@CurrentDate)-1),@CurrentDate)
this will return a new date by subtracting the value of -(DATEPART(dd,@CurrentDate)-1) from @CurrentDate value, which will be 09/01/2010 04:26:35:2222. Notice the minus "-" in front of the first parenthesis. This means to subtract.
To finish off I use Convert to strip off the hours, minutes, seconds and nanoseconds like so: convert(char(10), ...,101).
Resulting in a character string that will look like this: 09/01/2010
Finally I will convert that value back to DateTime so that I may use as such using the code in italics:convert( datetime, convert(char(10), dateadd(dd,-(DATEPART(dd,@CurrentDate)-1),@CurrentDate),101)) and I assign it to a return variable @FirstDay.
This value will set to 9/1/2010 00:00:00:0000 which is to say the first of month at midnight
Here is content of the function. You can copy and paste directly into SSMS and Execute to create the function.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: KLANGUEDOC
-- Description: Get the first day of the month
CREATE FUNCTION dbo.GetFirstDayOfMonth
declare @FirstDay datetime
set @FirstDay = (select convert( datetime, convert(char(10), dateadd(dd,-(DATEPART(dd,@CurrentDate)-1),@CurrentDate),101)))
-- Return the result of the function
There you have it. Another one for the books