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

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

  • Why not avoid the datatype conversions completely?

    declare @Today date

    declare @NowTime time

    select @Today = getdate()

    ,@NowTime = getdate()

    select @Today

    ,@NowTime

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

  • 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:-)

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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