Тёмный

Calculating the first day of the week in SQL Server 

SQL Server 101
Подписаться 17 тыс.
Просмотров 6 тыс.
50% 1

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

Опубликовано:

 

3 окт 2024

Поделиться:

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист
Посмотреть позже
Комментарии : 3   
@josealeal5312
@josealeal5312 3 месяца назад
OMG. This was super helpful. Also very clear to follow. Much appreciated.
@nikollas_pimentel
@nikollas_pimentel 8 месяцев назад
you was save my life! :D
@shadowitself
@shadowitself Год назад
love Ur movies logic + perfect english :D pls carry on :)
Далее
Обменялись песнями с POLI
00:18
Просмотров 535 тыс.
V16 из БЕНЗОПИЛ - ПЕРВЫЙ ЗАПУСК
13:57
🦊🔥
00:16
Просмотров 634 тыс.
UUID vs INT: What’s Better For Your Primary Key?
9:40