Re: Trigger For DateTime

(wackyphill@xxxxxxxxx) writes:
> Can someone give me a simple example of how to force a datetime value
> to be stripped of the time portion when it is added/updated to a table.
> I intend to use it for date only.
>
> I figured a trigger was the way to do this but haven't gotten the
> syntax right. Can someone please show me how? I've never written a
> trigger before.

CREATE TRIGGER tbl_tri ON tbl FOR INSERT, UPDATE AS
UPDATE tbl
SET datecol = convert(char(8), t.datecol, 112)
FROM tbl t
JOIN inserted i ON t.keycol1 = i.keycol1
AND t.keycol2 = i.keycol2
...


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
.

Relevant Pages

  • Re: Update Identity Column
    ... Actually, I implemented the trigger, and it seems to be working fine. ... Sylvain Lafontaine, ing. ... SQL Server will almost invariably decide that that's the best order to ... INSERT INTO tbl VALUES; ...
    (microsoft.public.access.adp.sqlserver)
  • Re: need help regarding finding rows modifeid during certain time frame
    ... the time period might be an option, I might be wrong here again. ... CREATE TRIGGER modified_tri ON tbl FOR INSERT, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: cumulative column in table
    ... UPDATE tbl ... there is no construct to do running sums efficiently in SQL Server. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.programming)
  • Re: cumulative column in table
    ... what is the method to populate the column cumulative_mount? ... UPDATE tbl ... there is no construct to do running sums efficiently in SQL Server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger
    ... I think the point of my trigger is supposed to keep the country ... CREATE TRIGGER no_more_teams ON tbl AFTER INSERT, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)