Scott on Writing

# re: Returning the First Day of the Month from a Given Date/Time value in SQL Server 12/6/2006 8:58 AM Mike Hamilton

Your example says if we are using December 12 and subtract the day (16) minus 1 to subtract 15...

???

I am guessing you changed the date and missed the rest of the line.

# re: Returning the First Day of the Month from a Given Date/Time value in SQL Server 12/6/2006 9:36 AM Scott Mitchell

Good catch, Mike. I've fixed this typo in the blog entry. Good to know at least one person out there reads my blog! :-p

# re: Returning the First Day of the Month from a Given Date/Time value in SQL Server 12/13/2006 9:55 AM Ben Strackany

For sake of completion, a third (dirty) way would be to exploit the fact that datetimes are numeric and 1.0 equals a day. So to get the beginning of the month from July 15, 2006, you could write your DAY() subtraction technique as

declare @mydate datetime
declare @startofmonth datetime
set @mydate = '20060715'
set @startofmonth = @mydate - DAY(@mydate) + 1
print @startofmonth

Obviously it would be best to stick with documented methods instead of the above. But I thought it was interesting.

# re: Returning the First Day of the Month from a Given Date/Time value in SQL Server 12/13/2006 11:43 AM JoeBeam

--get first day of month
set @first_day = DATEADD(mm, DATEDIFF(mm,0,@month_year), 0)
--get the first day of the next month
set @first_day_next_month = DATEADD(mm, DATEDIFF(mm,0,@month_year) + 1, 0)

# re: Returning the First Day of the Month from a Given Date/Time value in SQL Server 2/6/2007 11:03 AM Deathtospam

-- Declare the local variables.
DECLARE @dtValue datetime, @dtFirstOfMonth datetime

-- Initialize [@dtValue] with an arbitrary datetime value.
SELECT @dtValue = GETDATE()

-- Initialize [@dtFirstOfMonth] with a datetime value of
-- the first day of the month/year specified by [@dtValue].
SELECT @dtFirstOfMonth = CAST((CAST(YEAR(@dtValue) AS varchar) + '-' + CAST(MONTH(@dtValue) AS varchar) + '-1') AS datetime)

-- Display the information for viewing in Query Analyzer.
SELECT @dtValue AS dtValue, @dtFirstOfMonth AS dtFirstOfMonth