Wednesday, April 27, 2005

Days of the month using SQL

To find the days in a month using SQL, use the following method. Here TSQL syntax is used for example.

--------------------
DECLARE @Date datetime
SET @Date = '2000/02/1'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'
--------------------

for more sql tricks goto.. Extremeexperts
at

6 comments:

  1. Anonymous1/9/05 11:17 am

    should rather point to something open source like postgresql

    ReplyDelete
  2. மடல்காரன்_MadalKaran18/6/07 7:28 pm

    Very useful Tips.. great..! I like that.

    ReplyDelete
  3. Guru Prasath19/6/07 9:39 pm

    Thanks

    ReplyDelete
  4. Brandon9/1/09 6:13 am

    This totally saved me time. Thank you so much.

    ReplyDelete
  5. Unknown1/11/09 4:03 pm

    SELECT DAY(DATEADD(d,-1,dateadd(m,1,@Date))) AS 'Last day of the month'

    ReplyDelete
  6. Mr.Viet1/2/12 9:27 am

    Create FUNCTION [dbo].[ufn_GetLastDayOfMonth]
    (
    @pInputDate DATETIME
    )
    RETURNS DATETIME
    BEGIN

    DECLARE @vOutputDate DATETIME

    SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) -
    (DAY(@pInputDate) - 1) AS DATETIME)
    SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))

    RETURN @vOutputDate

    END

    ReplyDelete