Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2010
    Posts
    335

    Unanswered: Trigger Question

    Hi all,

    i'm not that familar with SQL-Server and i've a small issue creating a trigger.

    There's a table with a datetime Field. We want to normalize the data to the date at '12:00 AM' (e.g '2012-10-19 15:11:59.373' should be converted to '2012-10-19 00:00:00.000').
    I can change it for the existing data, but i also want that data which is inserted or updated is also converted. Now i've trouble to create an trigger which "corrects" the dates during insert and update.

    The Database belongs to an bought Application. So i can't change the Application nor the Datamodell.

    Edit: SQL-Server 2005

    Cheers
    nvk
    Last edited by nvk@vhv; 10-19-12 at 10:59.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I think I've achieved this in the past with the following expression:
    Code:
    DateValue = CAST(FLOOR(CAST([DateTimeValue] AS float)) AS datetime)
    This was working in SQL 2000 - I haven't had a chance to fully investigate what SQL 2005 can do with date/time values yet.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jan 2010
    Posts
    335
    Hi weejas,

    thanks for your quick answer.
    I know how how to convert the existing data and converting it the same way as you suggested. I just don't know how to implement in a trigger to adjust new or updated dates.

    Cheers
    nvk

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It's a shame you are not on SQL 2008. You could just convert the column to the DATE datatype, and the time portions would silently be discarded.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Ah, sorry. I'm having trouble focussing through the fog of anti-cold medication!

    Have a go with an AFTER trigger (insert and after) on this table, using the key value(s) from inserted to control which records to amend, and apply the above expression to the date field concerned. That way, whenever a record is inserted or amended, the date/time value will be truncated back to midnight.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Jan 2010
    Posts
    335
    This freaktion trigger-Statement drives me crazy:
    create trigger blablub_trigger
    on dbo.blablub
    after insert,update
    as
    begin


    update dbo.blablub
    set DATE_FIELD = CAST(FLOOR(CAST(DATE_FIELD AS float)) AS datetime
    from inserted as i
    where blablub.DATE_FIELD = i.DATE_FIELD;
    end
    ;
    results in
    Msg 156, Level 15, State 1, Procedure blablub_trigger, Line 10
    Incorrect syntax near the keyword 'from'.

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Code:
    UPDATE ProblemTableNameHere
    SET p.DateField = CAST(FLOOR(CAST p.DateField AS float)) AS datetime
    FROM
    inserted i
    INNER JOIN
    ProblemTableNameHere p
    ON i.KeyValue = p.KeyValue
    Might work - it's been a while since I wrote a SQL trigger, and right now I'm up to my eyeballs in Lotus Notes design.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You look to be missing an end parenthesis after "datetime".

  9. #9
    Join Date
    Jan 2010
    Posts
    335
    Nope,

    does not work for me ...

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ahh, so you have three functions, and only two sets of parentheses. Try
    Code:
    CAST(FLOOR(CAST (p.DateField AS float)) AS datetime)

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    MCrowley - thanks, I thought that something looked off. I'm still blaming the the drugs...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    Jan 2010
    Posts
    335
    Now it worked.
    Thx a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •