Technical Article

t-sql first last day date of the month

,

If you need calculate the fisrt or the last day date of a given date this stored procedure can help you

Description: returns the first or last day date
of the month
@d // the date ; if null is the current date
@FL first or last date day of the month
for 1 is the first day otherwise the last

testing:

DECLARE @R AS DATETIME
EXECUTE dbo.Usp_FirstLastMonthDayDate @R=@R,@FL=2, @D='20000223'

returns:

2000-02-29 00:00:00.000

 

-- with default values

DECLARE @R AS DATETIME
EXECUTE dbo.Usp_FirstLastMonthDayDate @R=@R

returns:

2009-11-01 23:10:37.393

 

-- =============================================
-- Author:Bernabe Diaz
-- Description:returns the first or last day date
-- of the month
-- @d // the date ; if null is the current date
-- @FL first or last date day of the month
-- for 1 is the first day otherwise the last
-- =============================================
CREATE PROCEDURE Usp_FirstLastMonthDayDate 
@d AS DATETIME=NULL,
@FL AS INT=1,
@R AS DATETIME OUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF @d IS NULL
BEGIN
SELECT @d=GETDATE()
END

IF @FL=1
BEGIN
-- Return the first day date
SELECT @R=dateadd(day,-(datepart(day,@d)-1),@d)
END 
ELSE
BEGIN
-- Return the last day date
SELECT @R=dateadd(day,-(datepart(day,dateadd(mm,1,@d))),dateadd(mm,1,@d))
END

SELECT @R
END

Rate

3 (14)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (14)

You rated this post out of 5. Change rating