Calculating the first day of the week in SQL Server
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: https://rebrand.ly/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): https://rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): https://rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): https://rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): https://rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): https://rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
----
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
Смотрите видео Calculating the first day of the week in SQL Server онлайн, длительностью часов минут секунд в хорошем качестве, которое загружено на канал SQL Server 101 24 Февраль 2022. Делитесь ссылкой на видео в социальных сетях, чтобы ваши подписчики и друзья так же посмотрели это видео. Данный видеоклип посмотрели 5,962 раз и оно понравилось 87 посетителям.
