• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

SQL Trigger for a Datetime


I wan't to prgramm a trigger that sets the value in the column to NULL IF the datetime in the record is = 30.12.1899 00:00:00

Can anyone please help me how to do this?
0
mr-kenny
Asked:
mr-kenny
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean:

CREATE TRIGGER trg_set_null
  ON yourtable
 FOR INSERT, UPDATE
AS
  UPDATE t
     SET your_date_field = NULL
   FROM yourtable t
   JOIN INSERTED i
       ON i.primary_key_field = t.primary_key_field
     AND i.your_date_field <= convert(datetime, '1900-01-01', 120)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello mr-kenny,

CREATE TRIGGER tr_Tablenmae_INSERT
ON Tablenmae
FOR INSERT, update
AS
Update TableName
SET datecolumn = NULL
FROM TableName
inner join inserted on tableName.PrimaryKey = inserted.primarykey
where inserted.datecolumn = '18991230'
go

Regards,

Aneesh
0
 
mr-kennyAuthor Commented:
what is this join inserted doing?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's making sure the update only looks at the rows that are actually inserted/updated, instead of checking the whole table
0
 
mr-kennyAuthor Commented:
what is this inserted for? thanks for your help

If a new record is inserted in the Table the datetime in my Column called date should set to NULL if the datetime value was 30.12.1899 00:00:00

0
 
mr-kennyAuthor Commented:
in your scripst i'm not sure which names I have to replace can you please format them clearly?
0
 
mr-kennyAuthor Commented:
it worked thanks you both
0
 
mr-kennyAuthor Commented:
thanks
0
 
HainKurtSr. System AnalystCommented:
check here for triggers: http://technet.microsoft.com/en-us/library/ms189799.aspx
CREATE TRIGGER trg_set_null
    ON yourtable
   FOR INSERT, UPDATE
AS
  UPDATE t
     SET your_date_field = NULL
   FROM yourtable t
   JOIN INSERTED i
     ON i.primary_key_field = t.primary_key_field
    AND i.your_date_field = convert(datetime, '12/30/1899', 101);
GO;

Open in new window

0
 
mr-kennyAuthor Commented:
last question: how do I save the trigger in SQL Management Studio?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you run the (sql) script.
if you  "save" the sql to file, it will NOT be save inside the sql server
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now