SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SteveEClarke
SteveEClarke
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 457
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
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18265 Visits: 20407
Why not avoid the datatype conversions completely?

declare @Today date

declare @NowTime time

select @Today = getdate()
,@NowTime = getdate()

select @Today
,@NowTime




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
SteveEClarke
SteveEClarke
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 457
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
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18265 Visits: 20407
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
SteveEClarke
SteveEClarke
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 457
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
SteveEClarke
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 457
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
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18265 Visits: 20407
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
SteveEClarke
SteveEClarke
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 457
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
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18265 Visits: 20407
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:-)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search