How to update sql table column value dynamicllay based on another value?

Question

I have one table with the following structure and some values :

PK_ID Webinar_Name Start_Date Is_Active

1 w1 3/1/2016 True

2 w2 1/7/2016 True

3 w3 4/9/2016 True

Now i want the Is_Active column value to be updated dynamically (i.e : NOT after update,insert and delete) based on the date.. if its matches the current date so the Is_Active column value will set to be false.

I've tried to use triggers but it should be after some action like insert and update which is conflicts with my requirements.

I appreciate any help . thanks

cf_en · Accepted Answer · 2015-11-19 13:01:41Z

3

It looks like you should be able to use a computed column for this, though I've never tried it.

https://msdn.microsoft.com/en-GB/library/ms188300.aspx

I guess it'd look something like this:

ALTER TABLE webinars ADD ActiveFlag AS
    CASE WHEN Start_Date = CAST(GetDate() AS date) THEN 'True'
    ELSE 'False' END

answered Nov 19, 2015 at 13:01

cf_en

1,6611 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-11-20 02:15:59Z

2

Use a computed column:

alter table t add Is_Active as (case when cast(start_date as date) = cast(getdate() as date) then 0 else 1 end)

Of course, you might need to remove the column first.

Note: this uses 1 for true and 0 for false. These constants are not defined in SQL Server. You can use whatever values you like.

edited Nov 20, 2015 at 2:15

answered Nov 19, 2015 at 13:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

cf_en Over a year ago

You'd probably also want to remove the time part from the date returned by GetDate(), otherwise it'll only match at precisely midnight on the start date

Collectives™ on Stack Overflow

How to update sql table column value dynamicllay based on another value?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related