Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date Conversion Fails in Stored Procedure - But code works in a Trigger !? Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 4:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1450060
Posted Tuesday, May 7, 2013 5:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 5,047, Visits: 11,799
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1450064
Posted Tuesday, May 7, 2013 5:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1450065
Posted Tuesday, May 7, 2013 5:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 5,047, Visits: 11,799
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1450067
Posted Tuesday, May 7, 2013 5:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1450068
Posted Tuesday, May 7, 2013 5:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1450070
Posted Tuesday, May 7, 2013 5:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 5,047, Visits: 11,799
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1450071
Posted Tuesday, May 7, 2013 5:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:52 AM
Points: 177, Visits: 416
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.
Post #1450081
Posted Tuesday, May 7, 2013 5:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 5,047, Visits: 11,799
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1450083
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse