Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to get day difference from when the user registered to current date.
I have this scenario:
I have some fixed value in master table like [0,6,12,18,24,30,36,42 .....]
and suppose 
1. day difference is greater or equal than 1 and less than 6 then It should be return 1. 
2. day difference is greater than 6 and less than 12 then it should return 2 and so on.
3. day difference is greater than 12 and less than 18 then return 3.
4. day difference is greater than 18 and less than 24 then return 4.
.
.
.
So on.
I don't want to use case statements because values in master table can not be fix but value pattern will be fix. table value pattern is like that:
common difference between two consecutive values is 6 
if n=0 then 
n+1 = (0 + 6) => 6


What I have tried:

declare @day int;
declare @regdate datetime = '2019-12-09 19:24:19.623';
declare @currentDate datetime = GETDATE();
SET @day = (SELECT DATEDIFF(day, @regdate, @currentDate) % 6 FROM tblMembers  WHERE Id = 1)
PRINT @day
Posted
Updated 14-Dec-19 1:15am

1 solution

Since you are increasing by six each time, just using an integer divide by 6 and adding 1 will give you the value you want:
5  / 6 = 0
7  / 6 = 1
13 / 6 = 2
...
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900