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

Question

Post reply

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.

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

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

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

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 2

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: 246965 More actions · Answer 3

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

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 4

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 · Answer 5

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: 246965 More actions · Answer 6

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

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 7

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: 246965 More actions · Answer 8

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