SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Calculating the next beginning of month and the current end of month

Handling dates is always a fun challenge in T-SQL! Finding the current end of month and next months beginning of month is straight forward, but I like to find new ways to do things that take less coding, and hate date conversions that require a lot of manipulation of characters and concatenation.

This was what I came up with for avoiding character conversions and concatenation for finding the current BOM (beginning of month) and EOM (end of month) values. Adjust according to your needs. Cheers!

--use datediff from 0, ie default 1900 date, to calculate current months as int
declare @ThisMonth int = datediff(month,0,cast(getdate() as date))
--add 1 to the current month to get the next month
declare @NextBom date = dateadd(month,@ThisMonth+1,0)
-- subtract a day from the beginning of next month to get the current end of month, without worrying about 28, 30, or 31 days.
declare @ThisEom date = dateadd(day,-1,@NextBom)

select @ThisMonth 
select @NextBom
select @ThisEom


Sheldon Hull

After working in the mortgage industry for 7 years, I transitioned into Business Intelligence and began learning SQL and .NET. My goal is to integrate my business knowledge into my development to intelligently analyze and find solutions to problems. Blogging offers me an option to share what I've learned as well as receive feedback on better practices and solutions.


Leave a comment on the original post [www.bitbarbarian.com, opens in a new window]

Loading comments...