ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming

How-To Get First Day of the Month Using T-SQL (Transact-SQL)

Updated on October 11, 2011

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.





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


-- Description: Get the first day of the month

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

CREATE FUNCTION dbo.GetFirstDayOfMonth


@CurrentDate datetime


RETURNS datetime



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



There you have it. Another one for the books


    0 of 8192 characters used
    Post Comment

    • klanguedoc profile image

      Kevin Languedoc 5 years ago from Canada

      thanks for the feedback

    • profile image

      Mr_X 5 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

      Kevin Languedoc 5 years ago from Canada

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

    • profile image

      Joe 5 years ago

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