SQL Server Performance

start and end of month function

Discussion in 'Getting Started' started by contactjimmyus, Mar 28, 2008.

  1. Does there exist a buitin function to find the start and end of a month for an year in sql server ???
    Thanks
  2. FrankKalis Moderator

    There is no built-in function, but you can use the built-in datetime functions to calculate these dates. Here are two ways of doing this:
    DECLARE @inputdate datetime
    SET @inputdate = '20080731';

    SELECT
    DATEADD(MONTH,DATEDIFF(MONTH, 0, @inputdate), 0)
    , DATEADD(MONTH,DATEDIFF(MONTH, 0,@inputdate), 30)


    SELECT
    CONVERT(CHAR(6), @inputdate, 112) + '01' AS 'firstDayofMonth'
    , CONVERT(CHAR(8),DATEADD(day, -1, CONVERT(CHAR(6), DATEADD(MONTH, 1, @inputdate),112)
    + '01'),112) AS 'lastDayofMonth'
  3. Madhivanan Moderator

    More generalised

    DECLARE @inputdate datetime
    SET @inputdate = '20080731';
    select
    dateadd(month,datediff(month,0,@inputdate),0),
    dateadd(month,datediff(month,0,@inputdate)+1,-1)
  4. FrankKalis Moderator

    Pretty much the same, I'd say [;)]
  5. Madhivanan Moderator

    Well. I am confused with the number 30 [:)]
  6. FrankKalis Moderator

    SELECT CAST(30 AS datetime)
    It's the end of SQL Server's first month. Any number (difference in months') added to this date by using DATEADD(MONTH...) , will always result in another end-of-month date.

  7. Madhivanan Moderator

    [quote user="FrankKalis"]
    SELECT CAST(30 AS datetime)
    It's the end of SQL Server's first month. Any number (difference in months') added to this date by using DATEADD(MONTH...) , will always result in another end-of-month date.

    [/quote]
    Thanks. This is why I always call you as Datetime Specialist [:)]

Share This Page