Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Calculating sum of days/weeks RE: Calculating sum of days/weeks

  • February 6, 2018 at 12:00 pm

    #1978752

    astrid 69000 - Tuesday, February 6, 2018 11:22 AM

    Hi,
    I have a calendar table that have the days, I need to add the following columns
    Days in a month
    Days in a quarter
    Days in a year
    Weeks in a month
    Weeks in a quarter
    I am finding the same issue with calculating how long to someone birthday.
    Here is what I wrote


    --days in a month    datediff(day, [Date], dateadd(month, 1, [Date])), [Date]
    --days in a quarter  datediff(day, [Date], dateadd(quarter, 1, [Date]))
    --days in a year     datediff(day, [Date], dateadd(year, 1, [Date]))
    --weeks in a quarter datediff(WEEK, [Date], dateadd(QUARTER, 1, [Date]))

    Ideas please 🙂

    You're on the right track for days in a quarter and days in a year, but you need to use the first day in a quarter/year for the comparison.  You've already been pointed to a website of useful date functions which includes finding the first day in a period.

    Weeks in a quarter is more problematic, because week could be defined several different ways, and each of those ways would have a slightly different coding.  For instance, what day does your week start?  How do you want to handle quarters that start/end in the middle of the week?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA