Calculating the first day of the week in SQL Server
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
OMG. This was super helpful. Also very clear to follow. Much appreciated.
you was save my life! :D
love Ur movies
logic + perfect english :D
pls carry on :)