locked
Auto Update of the Table in sql Server 2008 RRS feed

  • Question

  • User-1426568392 posted

    I want update or change the status of a column in a table after 24 hours entering in the database.

    How i can achieve this one with code.

    I am using Sql server 2008

    Thanks.

    Tuesday, January 2, 2018 1:19 PM

All replies

  • User753101303 posted

    Hi,

    I assume you do have already a date/time creation date  for those rows ?  You could then use this information on the fly to know which rows are expired and you woan't have anything to update. For more flexibility you could have a separate expiration date/time column allowing to make some rows lasting a bit longer if needed.

    Else you would have to run an update either when requesting data or by using a SQL job which is less convenient. So in short if a status is based on a delay think about using a date/time telling when this row enteed or will enter into this state rather than just a column giving the current state.

    Tuesday, January 2, 2018 2:56 PM
  • User-1716253493 posted

    Seem like you don't need status column in the table, you need to write query only.

    SELECT CASE WHEN DateAdd(day,1, entereddate) < getdate()then 0 ELSE 1 END as Status

    Or add calculated column using similar formula

    Tuesday, January 2, 2018 10:19 PM
  • User364663285 posted

    Do you mean to do some change on table, per every 24 hours? If yes, you can also create scheduled job for this.

    Wednesday, January 3, 2018 7:54 AM
  • User-1426568392 posted

    Yes after 24 hours if status is not changed from Process to Confirm then the status should become again stock.

    It should be from database level (May be trigger or)

    Thanks

    Wednesday, January 3, 2018 8:47 AM
  • User347430248 posted

    Hi sth_syed,

    you can try to use query like below.

    update [Table_Name] set [Status] = 'Stock' WHERE Date_column < DATEADD(hh,-24,GETDATE()) and [Status]='Process';

    then you can create a Job and schedule it to execute above query after certain time interval.

    Reference:

    Creating a regularly scheduled job

    Regards

    Deepak

    Friday, January 5, 2018 5:39 AM
  • User-1716253493 posted

    You can update the data in display data page.

    Before showing the data, update the status first.

    Friday, January 5, 2018 6:56 AM