Thread: problem with hour

hi
if users account is locked then after some hour(coming from database) the account will be unlocked.if user try to login i am showing him message you account has been locked and after no of hours your account will be unlocked.

below is code

Code:

EXEC pr_Setting_Get @vPortalID, @nMemberID, @nProfileID, 93, 0, @vsSettingValue = @sSettingValue OUTPUT
IF @sSettingValue <> '0'
BEGIN
IF CAST(@sSettingValue AS INT) <= (SELECT FailedAttempts FROM [Profile] WHERE ProfileID = @nProfileID)
BEGIN	
--Check if Time to automatic un-lock of account (hours)
EXEC pr_Setting_Get @vPortalID, @nMemberID, @nProfileID, 147, 0, @vsSettingValue = @sUnlockSettingValue OUTPUT
IF @sUnlockSettingValue <> '0'
BEGIN
-- User logged in after locked time + setting value then unlock
IF DATEADD(hh, -1 * CAST(@sUnlockSettingValue AS INT), GETDATE()) > (SELECT FailedDate FROM [Profile] WHERE ProfileID = @nProfileID)
BEGIN
	UPDATE [Profile]
				SET FailedAttempts = 0
				WHERE ProfileID = @nProfileID				
END 
ELSE
BEGIN
	SELECT  @rlLockedHours = CONVERT(DECIMAL(5,2),DATEDIFF(hh, GETDATE() ,DATEADD(hh,CAST(@sUnlockSettingValue AS INT),FailedDate))) FROM [Profile] WHERE ProfileID = @nProfileID						

	RETURN -10
END					
END
ELSE
BEGIN
RETURN -7
END				
END 
END

see the bold part.what i want is suppose for user i added 1 hour lock time in setting then when after 5 attempts user's account has been locked faileddate gives the date & time of last failure attempt.

so when user locked i am getting 1 hour to remain for unlocking.but after again and again refresh still it is showing 1 hour reamining actually it should become 0.59 then 0.58 like this

what I am missing

Why are you converting a time value to a decimal?

What is this piece supposed to do?

Code:

DATEADD(hh,CAST(@sUnlockSettingValue AS INT),FailedDate)

What is @sUnlockSettingValue?

@sUnlockSettingValue is no of hours user will remain unlocked after last failed attempt login.see on my website i want to show user how long he will be locked.suppose sUnlockSettingValue = 1 hrs and after last failed login attempt user will remain locked for 1 hours.then again user comes after 10 minutes and try to login in that time the user will see message u will unlocked after 0.50 hrs. and so on .hope it make some sense.

the values for this @sUnlockSettingValue comes from varchar field.thats why i am converting it to int

What do you get when you do this?

Code:

SET  @rlLockedHours = SELECT DATEDIFF(hh, GETDATE() ,DATEADD(hh,CAST(@sUnlockSettingValue AS INT),FailedDate)) FROM [Profile] WHERE ProfileID = @nProfileID

it always shows you will be unlocked after 1 hour.even I refreshed it after gap of few minutes it always shows 1.but i want to show always current remaining time for unlock

and i tried your query getting error
Msg 156, Level 15, State 1, Procedure pr_Profile_Login, Line 179
Incorrect syntax near the keyword 'SELECT'.

so I tried this way
SELECT @rlLockedHours = DATEDIFF(hh, GETDATE() ,DATEADD(hh,CAST(@sUnlockSettingValue AS INT),FailedDate)) FROM [Profile] WHERE ProfileID = @nProfileID

and here sUnlockSettingValue = 1 hour .so after every refresh I am getting this message
You have had too many consecutive login failures and your account has now been locked out. Your account will be unlocked after 1 hour(s).
Click here to return

but if i refresh page after each minute in that case 1 hour should be less by 1 minute ans should show eaxct remaining time.in this case not 1 hour. now it becomes .59,.58 or so on

I think the problem here is that your are doing the datediff calculation requesting hours,which will never return the number of minutes remaining. Rather, do the calculation using 'mi' for minutes. It appears the datediff is rounding up any fractional hours to next highest value, thus 30 minutes becomes 1 hour.