Tags

, , , , ,

Following query will take date as a input parameter and  returns you first and last days of previous month, current month and next month.

SET NOCOUNT ON
DECLARE @MonthStartDate As datetime
DECLARE @MonthEndDate As datetime
DECLARE @today datetime
SELECT @today = convert(varchar(12) , getdate() , 101) 
--change date as per your requirement

--First & Last dates for previous month
SET @MonthStartDate = @today
SELECT @MonthStartDate = dateadd ( dd , - datepart ( dd , @MonthStartDate ) , @MonthStartDate+1 )
SELECT @MonthStartDate = dateadd(mm , -1 , @MonthStartDate)
SELECT @MonthEndDate = dateadd(dd , -1 , dateadd ( mm , +1 , @MonthStartDate))
SELECT @MonthStartDate AS StartDate , @MonthEndDate AS EndDate

--First & Last dates for current month
SET @MonthStartDate = @today
SELECT @MonthStartDate = dateadd ( dd , - datepart ( dd , @MonthStartDate ) , @MonthStartDate+1 )
SELECT @MonthEndDate = dateadd(dd , -1 , dateadd ( mm , +1 , @MonthStartDate))
SELECT @MonthStartDate AS StartDate , @MonthEndDate AS EndDate

--First & Last dates for next month
SET @MonthStartDate = @today
SELECT @MonthStartDate = dateadd ( dd , - datepart ( dd , @MonthStartDate ) , @MonthStartDate+1 )
SELECT @MonthStartDate = dateadd(mm , +1 , @MonthStartDate)
SELECT @MonthEndDate = dateadd(dd , -1 , dateadd ( mm , +1 , @MonthStartDate))
SELECT @MonthStartDate AS StartDate , @MonthEndDate AS EndDate

Result
first and last day of month - sql server

Use Convert function to format the out put.
convert(varchar(12) , getdate() , 101) — MM/dd/yyyy
convert(varchar(12) , getdate() , 103) — dd/MM/yyyy

Happy learning.

Advertisements