locked
Basic SQL Query Retrieve the Month name, days Between two given dates RRS feed

  • Question

  • The Below Details are my Input data:-

    NAME---WORK-START DATE--------WORK-END DATE

    A-------06/JAN/20------------------17/MAR/20

    B------17/JUNE/20------------------15/AUG/20

    I want to write SQL query for the below Output

    NAME---DATE-----------TOTAL DAYS-

    A-------JAN---------------31

    A------FEB----------------29

    A-------MAR---------------30

    Similar way "B" row want to populate..

    kindly suggest me a solution


    Wednesday, July 1, 2020 2:09 PM

All replies

  • This is not possible to do with the given data, cause workdays depend on the calendar which applies to your worker.
    Wednesday, July 1, 2020 2:43 PM
  • Is it Possible to retrieve the below OUTPUT (ONLY DAYS COUNT OF EACH MONTH)

    NAME---DATE-----------TOTAL DAYS A-------JAN---------------31 A------FEB----------------29 A-------MAR---------------30

    Similar way "B" row want to populate..

    kindly suggest me a solution

     
    Wednesday, July 1, 2020 2:47 PM
  • E.g.

    SELECT DAY(EOMONTH(GETDATE()));

    Thursday, July 2, 2020 7:44 AM
  • hi

    declare @start DATE = '2020-01-06'
    declare @end DATE = '2020-03-17'
    
    ;with months (date)
    AS
    (
        SELECT @start
        UNION ALL
        SELECT DATEADD(month, 1, date)
        from months
        where DATEADD(month, 1, date) < @end
    )
    select     [MonthName]    = DATENAME(mm, date),
               [MonthNumber]  = DATEPART(mm, date),  
               [Noofdays]  = DATEPART(dd, EOMONTH(date)),
               [MonthYear]    = DATEPART(yy, date)
    from months

    Thanks and Regards

    Laxmidhar sahoo

    Thursday, July 2, 2020 5:25 PM