InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 22:27:52 GMT20http://www.sqlservercentral.com/Forums/Topic1366178-392-1.aspxIf you want to get fancy-smancy, you could do it like this:[code="sql"]CREATE TABLE #Users (UserID VARCHAR(20) NOT NULL ,Created_DT DATETIME DEFAULT (GETDATE()) ,Expiry_DT DATETIME DEFAULT (DATEADD(hour, 2, GETDATE())) ,Is_Expired AS CASE WHEN GETDATE() > Expiry_DT THEN 'Y' ELSE 'N' END)INSERT INTO #Users (UserID)SELECT 'Dwain'SELECT * FROM #Users-- When User validatesUPDATE #Users SET Expiry_DT = '9999-12-31' WHERE UserID = 'Dwain'SELECT * FROM #UsersDROP TABLE #Users[/code]Now you have an Is_Expired flag you can query.[b]Edit:[/b] The only downside to this approach is you can't index on the computed Is_Expired column because it is not persisted, and you can't make it persisted (I believe) because it is non-deterministic.Mon, 01 Oct 2012 05:53:31 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1366178-392-1.aspx[quote][b]Jason-299789 (10/1/2012)[/b][hr]nice once dwain, never thought of that, though I suppose it depends on how much you can change the underlying database structure.[/quote]Actually I see this all the time. People create a field called status and then set it active/inactive. Without an "inactive" date, this is not a very extensible design because it means you can't mark the "thing" in the table as "to be deactivated on <some future date>."Mon, 01 Oct 2012 04:37:14 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1366178-392-1.aspxnice once dwain, never thought of that, though I suppose it depends on how much you can change the underlying database structure.Mon, 01 Oct 2012 04:19:48 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1366178-392-1.aspx[quote][b]dwain.c (10/1/2012)[/b][hr]Don't use "status." Use an expiry timestamp instead. Then use that when you query to see whether the entry has expired.[/quote]+1 :-DMon, 01 Oct 2012 03:52:06 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1366178-392-1.aspxDon't use "status." Use an expiry timestamp instead. Then use that when you query to see whether the entry has expired.Mon, 01 Oct 2012 03:45:12 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1366178-392-1.aspxThe only way that I can think of is to have an SQL Agent Job running on a schedule that updates the record that have expired. However unles you are running this every minute there will be a latency based on the schedule where some rows will not be expired until for the duration of the latency, so on a 10 minute schedule then you will have rows that may not expire for <expire duration> + Schedule, depending on when they were due to expire.Mon, 01 Oct 2012 02:27:44 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1366178-392-1.aspxhello friends I am working on a application where we are sending OTP as password to users but this OTP is active only for 2 hours. Now I want do some thing my database that update the status of OTP after 2 hours automaticaly according to it creation time. can any one help me?.Sat, 29 Sep 2012 04:22:59 GMTamitsingh308