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

• SteveEClarke

  SSCertifiable

  Points: 5161

  More actions

  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.

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  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

  ---

• SteveEClarke

  SSCertifiable

  Points: 5161

  More actions

  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.

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  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.

  ---

• SteveEClarke

  SSCertifiable

  Points: 5161

  More actions

  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.

• SteveEClarke

  SSCertifiable

  Points: 5161

  More actions

  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.

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  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.

  ---

• SteveEClarke

  SSCertifiable

  Points: 5161

  More actions

  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.

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

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

  ---