T-SQL : treat int as weekday-CodePudding

DECLARE @weekDayCounter int = 7;
SELECT @weekDayCounter   1
--returns  8

The question is how do I make T-SQL treat @weekDayCounter as a weekday number without writing case or if statements?

I want to know if there is a way to CONVERT/CAST that int into a weekday datatype, or have a number series that resets to the first number when the max number is exceeded (e.g. 31st of December 1 day = 1st of January not 32 December OR 23:59 1hr = 00:59 not 24:59)

SELECT @weekDayCounter   1 --returns 1

Any answers are appreciated!

CodePudding user response：

If you just want to increment a value in the range 1 through 7 you can use the modulus operator:

set @WeekDayCounter = @WeekDayCounter % 7   1;

CodePudding user response：

If you'd just like the number to wrap back to 1 every time it passes 7, one option would be:

SELECT  @weekDayCounter - (7 * FLOOR(@weekDayCounter/7)) 1

It just subtracts out 7 * (the number of times 7 goes into @weekDayCounter)