Trigger assistance- change milliseconds in datetime string

Question

Post reply

Trigger assistance- change milliseconds in datetime string

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
November 8, 2005 at 4:10 pm

#102913

Hello All,
The trigger has to do insert/update on a datetime column, but here is the caveat.
The milliseconds needs to be removed from the date: 2005-11-04T11:01:44.7930000-06:00 it needs to look like this 2005-11-04T11:01:44.0000000-06:00.
Simply speaking the format should be like this: yyyy-mm-dd hh:mm:ss AM/PM
So far I got something like this:
CREATE TRIGGER dbo.tr_updateLastUpdated
ON dbo.CompanyStations
FOR UPDATE
AS
BEGIN
    IF UPDATE(LastUpdated)
        UPDATE dbo.CompanyStations SET LastUpdated = CONVERT(GETDATE())
        WHERE LastUpdated IN (SELECT bar FROM inserted)
END
GO
Thanks for your help!
Dave

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

I hate triggers... almost as much as cursors.

But you can set the date column = convert( varchar(20), getdate(), 13)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ninja's_RGR'us

SSC Guru

Points: 294069

November 8, 2005 at 5:24 pm

#602930

Why this hate for triggers?

R.Lalchhanhima SSC Rookie Points: 37 More actions · Answer 3

hi friend,

You seem to be familiar and thorough with triggers. I am new to SQL Server and i want to create trigger, such that whenever primary key column in 'first' table is updated, all other columns in other tables to be updated with the new values where old columns are equal. I can do it using 'on update cascade', but when there is cycle, i can not do it. i hope you can help me with this. And i'm not clear about 'inserted' and 'deleted' tables.

the example of what i want to do look like this..

CREATE TRIGGER dbo.updt

ON dbo.letters

FOR UPDATE

AS

BEGIN

IF UPDATE(letter_code)

update dbo.lett_off

set letter_code=inserted.letter_code

where lett_off.letter_code=deleted.letter_code

END

GO

john-132685 SSCertifiable Points: 6134 More actions · Answer 4

Simple answer: They are, 99% of the time, used, written, designed, and implemented incorrectly, improperly, etc.

Or put another way, they are the frequent path/tool of those not properly designing an application.

I hate them less than cursors because, unlike cursors, there are proper times and places for them--though so very rare and far between.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Aah - the smoke still hasn't cleared from the 10 (or so) triggers that I just created and fired...I'm crushed by the fact that I'm resorting to this because of an "improperly designed application" -

The only thing I have against triggers is the fact that if you have too many of them it's easy to let it all get "out of control" resulting in much bloodshed & mayhem...so I usually give them a wide berth unless they are "the last resort"..

**ASCII stupid question, get a stupid ANSI !!!**