none
Getting Month ending Dates RRS feed

  • Question

  • Dears,

    I have a request to get the month end dates when I filter based on Year

    see below query (12 records)

    select 
    *
    from
       [AdventureWorksDW2017].[dbo].[DimDate]

    where [CalendarYear]=2018

    Regards,

    Thursday, January 23, 2020 7:55 AM

All replies

  • Not clear what for a result you expect, but we have an "End-of-month" function in T-SQL: EOMONTH (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, January 23, 2020 8:03 AM
  • select distinct [EnglishMonthName],
     EOMONTH([FullDateAlternateKey])
    from [AdventureWorksDW2017].[dbo].[DimDate]

    Thursday, January 23, 2020 2:22 PM
    Moderator
  • Hi Avinash,

    You can use EOMONTH() simply for this or else you can also try below one.

    If you want to find last day of month of any day , use like below-

    DECLARE @dtDate DATETIME;
    SET @dtDate = '1/24/2020'; --give here whatever date that you wish
    
    SELECT 
    DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0)) AS LastDatetime_AnyMonth,
    CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0)) AS DATE) AS LastDate_AnyMonth;
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Thursday, January 23, 2020 7:56 PM