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

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: KLANGUEDOC

-- Description: Get the first day of the month

-- =============================================

CREATE FUNCTION dbo.GetFirstDayOfMonth

(

@CurrentDate datetime

)

RETURNS datetime

AS

BEGIN

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

RETURN @FirstDay


END

GO


There you have it. Another one for the books

More by this Author


Comments 4 comments

Joe 4 years ago

Personally, I prefer this version: SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @mydate), 0) AS StartOfMonth


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Nice solution. There are many solutions to a problem. Thanks for the feedback.


Mr_X 4 years ago

cast(([DATEto] - DAY([DATEto])+1) as DATE )

And we have result like 01.01.2010, 01.02.2010 and so on.


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

thanks for the feedback

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working