Microsoft SQL Server forum - Trigger For DateTime

Author

Trigger For DateTime

wackyphill@yahoo.com

2005-07-19, 11:23 am

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.

Thanks for any help.

MGFoster

2005-07-19, 8:24 pm

wackyphill@yahoo.com wrote:
> 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.

Saw this in a --CELKO-- post:

here is a proprietary but fast way to trim off the time:

CAST (FLOOR (CAST (my_date AS FLOAT)) AS DATETIME) = my_date

use CEILING() and you go to the next day.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

wackyphill@yahoo.com

2005-07-19, 8:24 pm

Yeah, that make sense. The question is how do you apply that alteration
to my_date when my date is part or a record being inserted into the DB
by some unknown program.

I need to write a trigger or something to run that code to modify the
insert/update.

Erland Sommarskog

2005-07-19, 8:24 pm

(wackyphill@yahoo.com) 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@sommarskog.se

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

wackyphill@yahoo.com

2005-07-20, 7:23 am

Awesome Erland, thanks that's what I was looking for!

I've never seen a FROM / JOIN on an update statement before. I assume
the JOIN insures that only the rows in the table that match the rows in
the inserted table are modified rather than the whole table?

What's the FROM clause for though? Is it required? Or was it just so
you could alias the table to save typing?

Thanks again for your help.

Erland Sommarskog

2005-07-20, 8:23 pm

(wackyphill@yahoo.com) writes:
> Awesome Erland, thanks that's what I was looking for!
>
> I've never seen a FROM / JOIN on an update statement before. I assume
> the JOIN insures that only the rows in the table that match the rows in
> the inserted table are modified rather than the whole table?
>

Welcome to SQL Server! :-)

FROM in UPDATE and DELETE is an extension to SQL Server (both Microsoft
and Sybase) which is very, very useful. This makes an UPDATE or DELETE
to be just like a SELECT. Thus:

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
...

Is just like:

SELECT datecol = convert(char(8), t.datecol, 112)
FROM tbl t
JOIN inserted i ON t.keycol1 = i.keycol1
AND t.keycol2 = i.keycol2
...

There are two drawbacks with this syntax:

1) It is not portable. If you want your code to run on other DBMS's
as well, don't use it.
2) If your joins are incomplete, so that the corresponding SELECT would
have duplicate rows, the result of the UPDATE statement is
unpredictable.

On the other hand, say that you have something like:

UPDATE tbl
SET col = (SELECT SUM(b.othercol)
FROM othertbl b
WHERE tbl.keycol = b.keycol)

If you rewrite this in the proprietary syntax:

UPDATE tbl
SET col = b.othercolsum
FROM tbl a
JOIN (SELECT keycol, othercolsum = SUM(othercol)
FROM othertbl
GROUP BY keycol) AS b ON a.keycol = b.keycol

My experience is that the last performs considerably better than the
ANSI-compliant query above. This is even clearer when you need to set
more than one column.

> What's the FROM clause for though? Is it required? Or was it just so
> you could alias the table to save typing?

Yes, FROM is needed in this syntax, but you don't actually have to
repeat the target table. This is legal:

UPDATE tbl
SET datecol = convert(char(8), tbl.datecol, 112)
FROM inserted i
WHERE tbl.keycol1 = i.keycol1
AND tbl.keycol2 = i.keycol2
...

But I don't like this, as it's not the same as a SELECT. By the way, if
I use a correlated subquery for the SET clause, I still put in a FROM
just to be able to use an alias.

Finally, I should admit that using EXISTS (SELECT * FROM inserted...) in
this case is cleaner, but writing joins requires less thinking. :-)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

wackyphill@yahoo.com

2005-07-20, 8:24 pm

Wow, that's really excellent stuff. Thanks so much for explaining it
out.
I understood what you said except for EXISTS (SELECT * FROM
inserted...) being cleaner. I don't follow where that could be used
instead of a join. That's just going to be true all the time isn't it?

Erland Sommarskog

2005-07-20, 8:24 pm

(wackyphill@yahoo.com) writes:
> Wow, that's really excellent stuff. Thanks so much for explaining it
> out.
> I understood what you said except for EXISTS (SELECT * FROM
> inserted...) being cleaner. I don't follow where that could be used
> instead of a join. That's just going to be true all the time isn't it?

Nah, the WHERE clause was implied:

UPDATE tbl
SET datecol = convert(char(8), t.datecol, 112)
FROM tbl t
WHERE EXISTS (SELECT *
FROM inserted i
WHERE t.keycol1 = i.keycol1
AND t.keycol2 = i.keycol2
... )

Here I have the FROM clause only to be able to use an alias.

And, by the way, since this is a commen misunderstanding, I should say
that in SQL Server triggers fires once per *statement*. It's a common
mistake to assume that triggers fires once per row, which lures people
to write triggers that handles multi-row operation properly.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

wackyphill@yahoo.com

2005-07-21, 7:23 am

Good point Erland. Well, I learned alot. Thanks so much for your time,
I appreciate it!

Sponsored Links