Post reply

Date Conversion Fails in Stored Procedure - But code works in a Trigger !?

  • May 7, 2013 at 4:59 am

    #293107

    Hi All,

    I have a Trigger which basically using the INSERTED and DELETED Tables - populates an "Audit" table - with DATE, TIME and various fields. This works fine.

    But when I lay the same logic under a Stored procedure to fire every 30 minutes I get the dreaded message ;

    "Conversion failed when converting datetime from character string"

    The code is the same -

    DECLARE @CurrentDate DATETIME

    DECLARE @CurrentTime Char(10)

    SET @CurrentDate = CONVERT(char(10), GetDate(),126)

    Set @CurrentTime = CONVERT(VARCHAR(8),GETDATE(),108)

    the fields are the same in the tables -

    [Date] [datetime] NOT NULL,

    [Time] [char](10) NOT NULL

    The Trigger is -

    INSERT INTO ([Date],[Time])

    SELECT @CurrentDate, @CurrentTime

    The Stored Procedure is -

    OUTPUT @CurrentDate, @CurrentTime INTO

    any help would be appreciated -

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • May 7, 2013 at 5:19 am

    #1612917

    Why not avoid the datatype conversions completely?

    declare @Today date

    declare @NowTime time

    select @Today = getdate()

    ,@NowTime = getdate()

    select @Today

    ,@NowTime

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 7, 2013 at 5:22 am

    #1612918

    I am trying to acheive two fields, one with a DATE in and the second with a TIME in, for an audit log.

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • May 7, 2013 at 5:25 am

    #1612920

    SteveEClarke (5/7/2013)

    I am trying to acheive two fields, one with a DATE in and the second with a TIME in, for an audit log.

    That's what my code does.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 7, 2013 at 5:28 am

    #1612921

    Phil Parkin (5/7/2013)

    Why not avoid the datatype conversions completely?

    declare @Today date

    declare @NowTime time

    select @Today = getdate()

    ,@NowTime = getdate()

    select @Today

    ,@NowTime

    Sorry but the TIME Data Type is only on SQL 2008 - not on SQL 2005

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • May 7, 2013 at 5:30 am

    #1612923

    Unfortunately I cannot upgrade our production server for another 6 months - so am stuck with 2005.

    But my main issue - is the fact that it is workign fine under a Trigger - but not a Stored Procedure.

    Thanks for any help.

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • May 7, 2013 at 5:34 am

    #1612924

    OK - forgot about that. But at least you can change the date bit. The error message suggests that that is where your problem is occurring.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 7, 2013 at 5:46 am

    #1612933

    Totally amazing when you read a piece of code so many times and blind by your own perfections.

    Have a sandwich .... have a drink .... come back ....

    Only to discover that those pesky little code faries have put the columns in a different order !!

    So ( after correcting their meddling! ) putting the date field instead of the string field - amazingly it works brilliantly.

    Thanks for listening -

    Apologies for wasting any of your valuable time

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • May 7, 2013 at 5:47 am

    #1612935

    SteveEClarke (5/7/2013)

    Totally amazing when you read a piece of code so many times and blind by your own perfections.

    Have a sandwich .... have a drink .... come back ....

    Only to discover that those pesky little code faries have put the columns in a different order !!

    So ( after correcting their meddling! ) putting the date field instead of the string field - amazingly it works brilliantly.

    Thanks for listening -

    Apologies for wasting any of your valuable time

    Thanks for posting back and no problem - we all do it:-)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 9 posts - 1 through 8 (of 8 total)

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