Peter Larsson Blog

Patron Saint of Lost Yaks

How to get the Weekday and Nth from a date

You call this function with a date. The function returns a table with one record and 3 columns.
First column is Weekday; Monday = 1, Tuesday = 2, Wednesday = 3; Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7.
Second column is number of occurencies of that date since beginning of selected period type.
Third columns is number of occurencies left of that period type.

CREATE FUNCTION dbo.fnGetWeekdayAndNths

(
    @theDate DATETIME,
    @theType CHAR(1)
)
RETURNS TABLE
AS
RETURN (   SELECT 1 + DATEDIFF(DAY, -53690, @theDate) % 7 AS theWeekday,
                    1 +(theDelta - 1) / 7 AS Beginning,
                    DATEDIFF(DAY, DATEADD(DAY, -1, thePeriod), @theDate) / 7 - 1 AS Ending
            FROM    (
                        SELECT CASE UPPER(@theType)
                                    WHEN 'M' THEN DATEADD(MONTH, DATEDIFF(MONTH, -53690, @theDate), -53659)
                                    WHEN 'Q' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, -53690, @theDate), -53600)
                                    WHEN 'Y' THEN DATEADD(YEAR, DATEDIFF(YEAR, -53690, @theDate), -53325)
                                END AS thePeriod,
                                CASE UPPER(@theType)
                                    WHEN 'M' THEN DATEPART(DAY, @theDate)
                                    WHEN 'Q' THEN DATEDIFF(DAY, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @theDate), 0), DATEADD(QUARTER, DATEDIFF(QUARTER, -53690, @theDate), -53600))
                                    WHEN 'Y' THEN DATEPART(DAYOFYEAR, @theDate)
                                END AS theDelta
                    ) AS d
            WHERE   UPPER(@theType) IN('Y', 'Q', 'M')
        )