Password Expiration date

Transact-SQL
1

I need a SQL code to set password expiration date to current date if it is created 120days from todays date and not used.if the password is less than 120 days from todays date then set it expiration date plus 120 date.

2 
create table #savi(samaccountname  varchar(50), passwordexpirationdate date,
                   isUsed bit )

insert into #savi
select 'dvader', dateadd(dd, -120, getdate()), 0 union
select 'lskywalker', dateadd(dd, -119, getdate()), 1 union
select 'savitha', getdate() -12, 1


select *, DATEDIFF(dd, tgt.passwordexpirationdate, getdate()), 
dateadd(dd, 120, getdate()) 
from #savi tgt

update tgt
   set tgt.passwordexpirationdate = 
       case
		 when DATEDIFF(dd, tgt.passwordexpirationdate, getdate())  = 120 and isUsed = 0
			then getdate()
		 else dateadd(dd, 120, getdate())
	   end
 From #savi tgt 

 
 select * from #savi

drop table #savi
1 Like