CODEWITHSUNDEEP
asp.netsqlsql-servert-sql
HShbib
HShbib

Reputation: 1841

SQL:check whether a time column is greater than system time

I would like to know how to check whether a timestamp in a (StartTime Column) is greater than the system time. If greater, it should reset the time stamp into NULL

Is there a way to do that?

Regards.

Upvotes: 1

Views: 966

Answers (5)

Ben Tennen
Ben Tennen

Reputation: 445

Try...

CREATE TRIGGER tr_Product_InsteadOfTrigger 
ON Product
INSTEAD OF INSERT
AS

  IF @@ROWCOUNT > 0 -- limit nested recursion if no work to do
  BEGIN

    SET NOCOUNT ON;

    INSERT INTO Product
    (
      ProductId,
      PTime 
    )
    SELECT 
      i.ProductId, 
      CASE 
        WHEN i.PTime > GETUTCDATE() 
        THEN NULL 
        ELSE i.PTime 
      END
    FROM 
      inserted i

  END -- if work to do

GO

Upvotes: 0

John Gibb
John Gibb

Reputation: 10763

  create trigger tbl_reset_date
  on tbl
  after insert
  as

  update t
  set time = null
  from tbl t
   join inserted i
     on i.id = t.id
  where i.time > getdate();

Upvotes: 1

Marco
Marco

Reputation: 57603

In MySQL you could try:

UPDATE table SET time = NULL
WHERE time>NOW()

If you want it to be auto, try this:

CREATE TRIGGER upd BEFORE INSERT ON `table` 
FOR EACH ROW BEGIN 
    IF NEW.time>NOW() THEN SET NEW.time=NULL; END IF; 
END;

Upvotes: 1

Dustin Hodges
Dustin Hodges

Reputation: 4193

Declare @Now datetime = GETDATE()

Update tbl Set
    StartTime=NULL
Where StartTime > @Now

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56407

update table set ts_field = null where ts_field > now()

this is for mysql

Upvotes: 1

Related Questions