Calculating the first day of the week in SQL Server

Sdílet
Vložit
  • čas přidán 19. 08. 2024
  • How can you find the start of a week?
    My SQL Server Udemy courses are:
    70-461, 70-761 Querying Microsoft SQL Server with T-SQL: rebrand.ly/que...
    98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/dat...
    70-462 SQL Server Database Administration (DBA): rebrand.ly/sql...
    Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql...
    SQL Server Integration Services (SSIS): rebrand.ly/sql...
    SQL Server Analysis Services (SSAS): rebrand.ly/sql...
    Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/mic...
    ----
    You may want to group together dates by week starting. But how can you do this?
    Here is the start of my code, which populates the table:
    DROP TABLE IF EXISTS myDates
    GO
    CREATE TABLE myDates
    (create_date datetime);
    INSERT INTO myDates
    VALUES ('2024-02-01'), ('2024-02-02'), ('2024-02-03'), ('2024-02-04'), ('2024-02-05'), ('2024-02-06'), ('2024-02-07')
    , ('2024-02-08'), ('2024-02-09'), ('2024-02-10'), ('2024-02-11'), ('2024-02-12'), ('2024-02-13'), ('2024-02-14')
    ----
    Here is my code, which calculates the table. Please note - I am not allowed to use a less-than sign in these notes, so please substitute LESSTHAN with the less-than sign.
    declare @FirstDate as int = 5
    SELECT format(create_date,'ddd d MMM yyyy') as CreateDate,
    datepart(weekday, create_date) as WkDay,
    format(
    dateadd(day, @FirstDate-datepart(weekday, create_date)
    - case when datepart(weekday, create_date) LESSTHAN @FirstDate then 7 else 0 end, create_date)
    ,'ddd d MMM yyyy') as StartWeekDay
    FROM myDates
    ORDER BY create_date

Komentáře • 3

  • @josealeal5312
    @josealeal5312 Před 2 měsíci +1

    OMG. This was super helpful. Also very clear to follow. Much appreciated.

  • @nikollaspk
    @nikollaspk Před 6 měsíci +1

    you was save my life! :D

  • @shadowitself
    @shadowitself Před 10 měsíci +1

    love Ur movies
    logic + perfect english :D
    pls carry on :)