I have a simple, yet an interesting scenario; 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, using a Monday to Sunday week cycle, which obviously in not the default in SQL Server.

I know that if I write,

SET DATEFIRST 1

it solves the problem.

Unfortunately, I cannot use DATEFIRST function either.

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

How do I develop week numbers in a Monday to Sunday week cycle for the above BusinessDate column ?

Any suggestion would be appreciated.