Labels

getting the first and last day of the month in sql

astronomical clock courtesy of simpologisti was putting this together for a friend of mine so i thought i would post it since it seems like a pretty useful thing to have. should be self-explanatory.

-- first and last for previous month
DECLARE @FirstDayPrev DATETIME
DECLARE @LastDayPrev DATETIME
-- first and last for current month
DECLARE @FirstDayCurr DATETIME
DECLARE @LastDayCurr DATETIME
Set @FirstDayPrev = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0),101)
Set @LastDayPrev = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm, 0,getdate())+0, -1),101)
Set @FirstDayCurr = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm, 0,getdate())+0, 0),101)
Set @LastDayCurr = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm, 0,getdate())+1, -1),101)
Select @FirstDayPrev, @LastDayPrev, @FirstDayCurr, @LastDayCurr
Labels:

Comments

  1. Anonymous7/19/11, 3:41 AM

    Thanks for the nice post there! But when i run the query it show the time as '00:00' (12AM). So technically, if I select 12AM of last day of a current/previous month, dont I miss one complete day? So, is it possible to add time in this query, so I can add the last day with time 23:59 or something?
    ~Ãvinash
    (avinashkashyapa@gmail.com)

    ReplyDelete
  2. Marcus Oh7/19/11, 9:51 AM

    avinash - thanks for your comment. i subtracted one second from the @FirstDayCurr and got the output you're talking about:


    Select @FirstDayPrev, DateAdd(SS,-1,@FirstDayCurr)

    Result:
    2011-06-01 00:00:00.000 2011-06-30 23:59:59.000


    hope that helps.

    ReplyDelete

Post a Comment