Calculate Week Number of Year in T-SQL without DATEFIRST function or other database objects

I am working on a SaaS application that connects to a Microsoft SQL Server database, using T-SQL language.

I cannot develop stored procedures, user-defined functions, or any database objects.

I am developing a report using T-SQL language in my application; this report needs to generate week numbers (for a given year), using a Monday to Sunday week cycle, which obviously in not the default in Microsoft SQL Server.

I know that if I write,

SET DATEFIRST 1

it solves the problem.

Unfortunately, I cannot use DATEFIRST function either.

I can use a global variable such as @@DateFirst though.

I have a column called BusinessDate with date datatype in a table called dbo.BusinessInformation.

DATEPART(wk,BusinessDate) AS WeekNumberOfYear

How do I develop week number of a year in a Monday to Sunday week cycle, for the above BusinessDate column?

Any suggestion would be appreciated.

DECLARE @BusinessInformation TABLE ([BusinessDate] [date])

INSERT INTO @BusinessInformation

SELECT '2024-02-16'
UNION ALL
SELECT '2024-02-17'
UNION ALL
SELECT '2024-02-18'
UNION ALL
SELECT '2024-02-19'
UNION ALL
SELECT '2024-02-20'
UNION ALL
SELECT '2024-02-21'
UNION ALL
SELECT '2024-02-22'
UNION ALL
SELECT '2024-02-23'
UNION ALL
SELECT '2024-02-24'
UNION ALL
SELECT '2024-02-25'
UNION ALL
SELECT '2024-02-26'
UNION ALL
SELECT '2024-01-01'
UNION ALL
SELECT '2024-01-02'
UNION ALL
SELECT '2024-12-30'
UNION ALL
SELECT '2024-12-31'
UNION ALL
SELECT '2025-01-01'
UNION ALL
SELECT '2025-01-02'

SET DATEFIRST 7
--default--
--Sunday to Saturday week cycle--
--this CANNOT be changed in the SaaS application--

SELECT 
    BusinessDate, 
    DATENAME(dw, BusinessDate) AS WeekdayName,  
    DATEPART(wk,BusinessDate) AS ActualWeekNumberOfYear, 
    DATEADD(dd, -1, BusinessDate) AS SubtractDay, 
    DATEPART(wk,DATEADD(dd, -1, BusinessDate)) AS SubtractDayWeekNumberOfYear
FROM
    @BusinessInformation
ORDER BY 
    BusinessDate

In this code, SubtractDayWeekNumberOfYear works, but the start and/or tail of the year (Dec 31, Jan 1, etc.) have incorrect week numbers.

I need the output as below:

SET DATEFIRST 1
--Monday to Sunday week cycle--
--this is only for demonstration; I cannot change the DATEFIRST value 
--to 1 from 7, in the SaaS application--
--the output of the earlier T-SQL code must match that of this code; 
--that is the requirement of the project--
--in other words, I need to modify the former code to achieve the-- 
--output of this code--
  

SELECT  
    BusinessDate, 
    DATENAME(dw, BusinessDate) AS WeekdayName, 
    DATEPART(wk,BusinessDate) AS DATEFIRSTWeekNumberOfYear
FROM
    @BusinessInformation
ORDER BY 
    BusinessDate

I have even developed the below column:

(@@DateFirst + DatePart(weekday,BusinessDate) - 1) % 7 + 1

This gives me integer values from 1 to 7, but not sure how to develop week numbers using the 1 - 7 values.

Although this question looks pretty straightforward, and looks like someone has a solution in Stack Overflow already, that is not the case. I see a lot of similar posts, but not addressing my question directly!

Recommended topics

Hot tags