Convert date column into week

Question

Normally, I write the weeks of the month in my SQL query as follows but I will prepare a dashboard and this dashboard will be based on 2023. For this reason, I cannot write all months this way. How can I write this in SQL in an easy way, and the start date of the week will be Saturday.

Here is my query

select 
    dtp.MAIN_CUSTOMER_NUMBER, dtp.TRN_DATE,
    case
        when dtp.TRN_DATE between '20231002' and '20231009' 
            then 'Ekim 1.hafta'
        when dtp.TRN_DATE  between '20231009' and '20231016' 
            then 'Ekim 2.hafta'
        when dtp.TRN_DATE  between '20231016' and '20231023' 
            then 'Ekim 3.hafta'
        when dtp.TRN_DATE  between '20231023' and '20231030' 
            then 'Ekim 4.hafta'
        when dtp.TRN_DATE  between '20231030' and '20231106' 
            then 'Kasım 1.hafta'
        when dtp.TRN_DATE  between '20231106' and '20231112' 
            then 'Kasım 2.hafta'
from 
    BOACARD.TRN.DAILY_TRANSACTION_POOL dtp with (nolock)

Answer 1

If you first calculate WeekEndingDate (Sunday) with a bit of date arithmetic, you can extract the month name using DATENAME(month, WeekEndingDate) and the week number (within each month) using the calculation (DAY(ED.WeekEndingDate) + 6) / 7.

Resulting logic (including a date-generator).

SET LANGUAGE Turkish
SET DATEFIRST 1 -- Set Monday (Pazartesi) as first day of week

SELECT
    dtp.TRN_DATE,
    ED.WeekEndingDate,
    CONCAT(
        DATENAME(month, ED.WeekEndingDate),
        ' ',
        (DAY(ED.WeekEndingDate) + 6) / 7,
        '.hafta'
        ) AS Period
FROM (
    SELECT DATEADD(day, S.value, '20231001 12:34') AS TRN_DATE
    FROM GENERATE_SERIES(0, 50) S
) dtp
CROSS APPLY (
    SELECT DATEADD(
        day,
        7 - DATEPART(weekday, dtp.TRN_DATE),
        CONVERT(DATE, dtp.TRN_DATE)
        ) AS WeekEndingDate
) ED
ORDER BY dtp.TRN_DATE

However, the results are slightly different from your original mappings. I am not clear as to why 2023-10-01 is not week 1 (Ekim 1.hafta) and dates 2023-10-08 through 2023-10-08 is not week 2 (Ekim 2.hafta). In contrast, your posted logic places 2023-11-01 is not week 1 (Kasım 1.hafta).

If the selected month and week-number are based on a day other than the last day of the period (perhaps some day mid-week such as Thursday), you may need to calculate slightly different reference-date and perform your final calculations based on that.

CROSS APPLY (
    SELECT DATEADD(
        day,
        4 - DATEPART(weekday, dtp.TRN_DATE),  -- Mid-week Thursday
        CONVERT(DATE, dtp.TRN_DATE)
        ) AS AltReferenceDate
) RD

This assigns the week to the month having the most days (4 or more) from that week.

Results (showing both calcuations):

TRN_DATE	WeekEndDate	Period	AltRefDate	AltPeriod	Expected
2023-10-01 12:34	2023-10-01	Ekim-1.hafta	2023-09-28	Eylül 4.hafta
2023-10-02 12:34	2023-10-08	Ekim-2.hafta	2023-10-05	Ekim 1.hafta	Ekim 1.hafta
2023-10-03 12:34	2023-10-08	Ekim-2.hafta	2023-10-05	Ekim 1.hafta	Ekim 1.hafta
2023-10-04 12:34	2023-10-08	Ekim-2.hafta	2023-10-05	Ekim 1.hafta	Ekim 1.hafta
2023-10-05 12:34	2023-10-08	Ekim-2.hafta	2023-10-05	Ekim 1.hafta	Ekim 1.hafta
2023-10-06 12:34	2023-10-08	Ekim-2.hafta	2023-10-05	Ekim 1.hafta	Ekim 1.hafta
2023-10-07 12:34	2023-10-08	Ekim-2.hafta	2023-10-05	Ekim 1.hafta	Ekim 1.hafta
2023-10-08 12:34	2023-10-08	Ekim-2.hafta	2023-10-05	Ekim 1.hafta	Ekim 1.hafta
2023-10-09 12:34	2023-10-15	Ekim-3.hafta	2023-10-12	Ekim 2.hafta	Ekim 2.hafta
...	...	...	...	...	...
2023-10-15 12:34	2023-10-15	Ekim-3.hafta	2023-10-12	Ekim 2.hafta	Ekim 2.hafta
2023-10-16 12:34	2023-10-22	Ekim-4.hafta	2023-10-19	Ekim 3.hafta	Ekim 3.hafta
...	...	...	...	...	...
2023-10-22 12:34	2023-10-22	Ekim-4.hafta	2023-10-19	Ekim 3.hafta	Ekim 3.hafta
2023-10-23 12:34	2023-10-29	Ekim-5.hafta	2023-10-26	Ekim 4.hafta	Ekim 4.hafta
...	...	...	...	...	...
2023-10-29 12:34	2023-10-29	Ekim-5.hafta	2023-10-26	Ekim 4.hafta	Ekim 4.hafta
2023-10-30 12:34	2023-11-05	Kasım-1.hafta	2023-11-02	Kasım 1.hafta	Kasim 1.hafta
...	...	...	...	...	...
2023-11-05 12:34	2023-11-05	Kasım-1.hafta	2023-11-02	Kasım 1.hafta	Kasim 1.hafta
2023-11-06 12:34	2023-11-12	Kasım-2.hafta	2023-11-09	Kasım 2.hafta	Kasim 2.hafta
2023-11-07 12:34	2023-11-12	Kasım-2.hafta	2023-11-09	Kasım 2.hafta	Kasim 2.hafta
2023-11-08 12:34	2023-11-12	Kasım-2.hafta	2023-11-09	Kasım 2.hafta	Kasim 2.hafta
2023-11-09 12:34	2023-11-12	Kasım-2.hafta	2023-11-09	Kasım 2.hafta	Kasim 2.hafta
2023-11-10 12:34	2023-11-12	Kasım-2.hafta	2023-11-09	Kasım 2.hafta	Kasim 2.hafta
2023-11-11 12:34	2023-11-12	Kasım-2.hafta	2023-11-09	Kasım 2.hafta	Kasim 2.hafta
2023-11-12 12:34	2023-11-12	Kasım-2.hafta	2023-11-09	Kasım 2.hafta	???
2023-11-13 12:34	2023-11-19	Kasım-3.hafta	2023-11-16	Kasım 3.hafta

See this db<>fiddle

Answer 2

In SQL Server you can use DATEPART() funciton for example

SELECT DATEPART(wk,TRN_DATE) 

Will return an integer for the "week number"

doc - https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver16

Here is how to do it in Oracle

SELECT to_number(to_char(to_date(TRN_DATE,'MM/DD/YYYY'),'WW'))

doc - https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html#GUID-E118F121-A0E1-4784-A685-D35CE64B4557

---

Note the following is considered Standard SQL and is used in Big Query

SELECT extract(week from TRN_DATE)

Answer 3

I didn't understand your logic exactly but it looks like you are after something like this (not a constant as 'Ekim' and week of year):

SET DATEFIRST 6;
SET LANGUAGE turkish;

WITH dates (d)
AS (SELECT DATEADD(d, N - 1, CAST('20230101' AS DATE))
    FROM
    (
        SELECT TOP (366)
               ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
        FROM master.sys.all_columns t1
            CROSS JOIN master.sys.all_columns t2
    ) tmp )
SELECT d,
       DATENAME(MONTH, d) + ' '
       + CAST(DATEPART(WEEK, d) - DATEPART(WEEK, DATEFROMPARTS(YEAR(d), MONTH(d), 1)) + 1 AS VARCHAR) + '.haftasi'
FROM dates;

DBFiddle demo