Help on these questions

Transact-SQL
,
1

I have a question which I faced for a t-sql interview. I did not get much clue how to fetch the values correctly.
a) I want to get the saturdays and sundays dates of the current month.
b) Want to fetch the first saturday and last saturday of the current month.

Thank You in advance for the help. Could be ideal if you can explain the code.

2

Not sure if this might help:
http://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server

3

:fearful: Avoid messing with DATEFIRST settings and/or calcs. Fortunately you don't need them.

A calendar is actually very simple: the same 7 days repeat, in order, forever. Therefore, simple date calcs can give you any such results.

I use a consistent method for all such date computations, as below. Once you get used to the idea of a displacement off a base date, the code is actually more straightforward than it looks. The only thing I've left off the first code is a check to see if the last Sunday is still in the current month, you'll need to adjust for that yourself.

And hopefully the names explain the code -- i.e. "self-documenting" code as much as possible -- but naturally feel free to ask additional qs.

;WITH
cteTally AS (
    SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS numbers(week#)
)
SELECT 
    DATEADD(DAY, week# * 7, current_month_saturday) AS current_month_saturday, 
    DATEADD(DAY, 1, DATEADD(DAY, week# * 7, current_month_saturday)) AS current_month_sunday
FROM (
    SELECT CAST(DATEADD(DAY, 6, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS date) AS current_month_day7,
        5 AS saturday
) AS base_dates
CROSS APPLY (
    /* back up to the first Saturday of the current month */
    SELECT DATEADD(DAY, -DATEDIFF(DAY, saturday, current_month_day7) % 7, current_month_day7) AS current_month_saturday
) AS date_calc_to_determine_first_saturday
INNER JOIN cteTally weeks ON DATEDIFF(MONTH, 0, DATEADD(DAY, week# * 7, current_month_saturday)) = DATEDIFF(MONTH, 0, GETDATE())


SELECT current_month_saturday, current_month_last_saturday
FROM (
    SELECT CAST(DATEADD(DAY, 6, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS date) AS current_month_day7,
        CAST(DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) AS date) AS current_month_last_day,
        5 AS saturday
) AS base_dates
CROSS APPLY (
    /* back up to the first Saturday of the current month */
    SELECT DATEADD(DAY, -DATEDIFF(DAY, saturday, current_month_day7) % 7, current_month_day7) AS current_month_saturday,
    /* back up to the last Saturday of the current month */
        DATEADD(DAY, -DATEDIFF(DAY, saturday, current_month_last_day) % 7, current_month_last_day) AS current_month_last_saturday
) AS date_calcs_to_determine_saturdays
1 Like