Implicit conversion from datetime to int not allowed

microsoft.public.sqlserver.programming

4 Sep 2006 4:03 PM

Assimalyst

Hi,

I have the following stored procedure, which selects a single dattime
value. I wan't to return this value but am unable to save the sp
because of the following error:

"Implicit conversion from data type datetime to int is not allowed. Use
the CONVERT function to run this query"

But i can't get it working.

Here's the sp:

@fkiCardID varchar(8),
@dtLastLog datetime

AS

SET @dtLastLog = (SELECT TOP 1 dtLogTime
FROM tblUsageLog
WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
ORDER BY dtLogTime DESC)

RETURN @dtLastLog

Any ideas on how to fix it?

Thanks

4 Sep 2006 4:21 PM

Arnie Rowland

Based on the scant information provided, I would guess that dtLogTime is an
integer, in the form of 2006090409190000 -or something like that.

Please send the DDL for tblUsageLog.

It would help us better assist you if you could include table DDL, query
strategy used so far, sample data in the form of INSERT statements, and an
illustration of the desired results. (For help with that refer to:
http://www.aspfaq.com/5006 )

The less 'set up' work we have to do, the more likely you are going to have
folks tackle your problem and help you. Without this effort from you, we are
just playing guessing games.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quoteHide quote

"Assimalyst" <c_oxt***@hotmail.com> wrote in message
news:1157385807.516044.160310@i42g2000cwa.googlegroups.com...
> Hi,
>
> I have the following stored procedure, which selects a single dattime
> value. I wan't to return this value but am unable to save the sp
> because of the following error:
>
> "Implicit conversion from data type datetime to int is not allowed. Use
> the CONVERT function to run this query"
>
> But i can't get it working.
>
> Here's the sp:
>
> @fkiCardID varchar(8),
> @dtLastLog datetime
>
> AS
>
> SET @dtLastLog = (SELECT TOP 1 dtLogTime
> FROM tblUsageLog
> WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
> ORDER BY dtLogTime DESC)
>
> RETURN @dtLastLog
>
> Any ideas on how to fix it?
>
> Thanks
>

Are all your drivers up to date? click for free checkup

4 Sep 2006 9:00 PM

Steve Kass

Assimalyst,

The return value of a stored procedure must be an int. It can't be a
datetime value, and it's not really the right place to return this kind
of information to the caller. The result value is more appropriate for
returning error conditions or completion codes. If you want to
calculate a datetime value and get it back to the caller, you have a
couple of choices:

1. write a function instead of a stored procedure:

create function f(
@fkiCardID varchar(8),
@dtLastLog datetime
) returns datetime as begin
(SELECT TOP 1 dtLogTime
FROM tblUsageLog
WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
ORDER BY dtLogTime DESC)
end

(and call it accordingly)

2. use an OUTPUT parameter and return the datetime value through it:

create procedure p (
@fkiCardID varchar(8),
@dtLastLog datetime,
@result datetime output
) as
SET @result =
(SELECT TOP 1 dtLogTime
FROM tblUsageLog
WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
ORDER BY dtLogTime DESC)

In this case, you'll need to restate OUTPUT in the procedure call.

exec p @thisval, @thatval, @result OUTPUT

Steve Kass
Drew University
www.stevekass.com

Assimalyst wrote:

Show quoteHide quote

>Hi,
>
>I have the following stored procedure, which selects a single dattime
>value. I wan't to return this value but am unable to save the sp
>because of the following error:
>
>"Implicit conversion from data type datetime to int is not allowed. Use
>the CONVERT function to run this query"
>
>But i can't get it working.
>
>Here's the sp:
>
>@fkiCardID varchar(8),
>@dtLastLog datetime
>
>AS
>
>SET @dtLastLog = (SELECT TOP 1 dtLogTime
>FROM tblUsageLog
>WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
>ORDER BY dtLogTime DESC)
>
>RETURN @dtLastLog
>
>Any ideas on how to fix it?
>
>Thanks
>
>
>

5 Sep 2006 1:54 AM

Mike C#

Just one addition to Steve's answer: you can also return the value as a
result set. For a single datetime value you're probably better off with a
single OUTPUT parameter though.

Show quoteHide quote

"Steve Kass" <sk***@drew.edu> wrote in message
news:uv56mUG0GHA.4228@TK2MSFTNGP06.phx.gbl...
> Assimalyst,
>
> The return value of a stored procedure must be an int. It can't be a
> datetime value, and it's not really the right place to return this kind of
> information to the caller. The result value is more appropriate for
> returning error conditions or completion codes. If you want to calculate
> a datetime value and get it back to the caller, you have a couple of
> choices:
>
> 1. write a function instead of a stored procedure:
>
> create function f(
> @fkiCardID varchar(8),
> @dtLastLog datetime
> ) returns datetime as begin
> (SELECT TOP 1 dtLogTime
> FROM tblUsageLog
> WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
> ORDER BY dtLogTime DESC)
> end
>
> (and call it accordingly)
>
> 2. use an OUTPUT parameter and return the datetime value through it:
>
> create procedure p (
> @fkiCardID varchar(8),
> @dtLastLog datetime,
> @result datetime output
> ) as
> SET @result =
> (SELECT TOP 1 dtLogTime
> FROM tblUsageLog
> WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
> ORDER BY dtLogTime DESC)
>
>
> In this case, you'll need to restate OUTPUT in the procedure call.
>
> exec p @thisval, @thatval, @result OUTPUT
>
>
> Steve Kass
> Drew University
> www.stevekass.com
>
> Assimalyst wrote:
>
>>Hi,
>>
>>I have the following stored procedure, which selects a single dattime
>>value. I wan't to return this value but am unable to save the sp
>>because of the following error:
>>
>>"Implicit conversion from data type datetime to int is not allowed. Use
>>the CONVERT function to run this query"
>>
>>But i can't get it working.
>>
>>Here's the sp:
>>
>>@fkiCardID varchar(8),
>>@dtLastLog datetime
>>
>>AS
>>
>>SET @dtLastLog = (SELECT TOP 1 dtLogTime
>>FROM tblUsageLog
>>WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
>>ORDER BY dtLogTime DESC)
>>
>>RETURN @dtLastLog
>>
>>Any ideas on how to fix it?
>>
>>Thanks
>>
>>

5 Sep 2006 8:58 AM

Assimalyst

Thanks for the replys guys, went with Steves option 2 and all is well!

Steve Kass wrote:
Show quoteHide quote

> Assimalyst,
>
> The return value of a stored procedure must be an int. It can't be a
> datetime value, and it's not really the right place to return this kind
> of information to the caller. The result value is more appropriate for
> returning error conditions or completion codes. If you want to
> calculate a datetime value and get it back to the caller, you have a
> couple of choices:
>
> 1. write a function instead of a stored procedure:
>
> create function f(
>   @fkiCardID varchar(8),
>   @dtLastLog datetime
> ) returns datetime as begin
> (SELECT TOP 1 dtLogTime
> FROM tblUsageLog
> WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
> ORDER BY dtLogTime DESC)
> end
>
> (and call it accordingly)
>
> 2. use an OUTPUT parameter and return the datetime value through it:
>
> create procedure p (
>   @fkiCardID varchar(8),
>   @dtLastLog datetime,
>   @result datetime output
> ) as
> SET @result =
> (SELECT TOP 1 dtLogTime
> FROM tblUsageLog
> WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
> ORDER BY dtLogTime DESC)
>
>
> In this case, you'll need to restate OUTPUT in the procedure call.
>
> exec p @thisval, @thatval, @result OUTPUT
>
>
> Steve Kass
> Drew University
> www.stevekass.com
>
> Assimalyst wrote:
>
> >Hi,
> >
> >I have the following stored procedure, which selects a single dattime
> >value. I wan't to return this value but am unable to save the sp
> >because of the following error:
> >
> >"Implicit conversion from data type datetime to int is not allowed. Use
> >the CONVERT function to run this query"
> >
> >But i can't get it working.
> >
> >Here's the sp:
> >
> >@fkiCardID varchar(8),
> >@dtLastLog datetime
> >
> >AS
> >
> >SET @dtLastLog = (SELECT TOP 1 dtLogTime
> >FROM tblUsageLog
> >WHERE fkiCardID = @fkiCardID AND szInOut = 'In'
> >ORDER BY dtLogTime DESC)
> >
> >RETURN @dtLastLog
> >
> >Any ideas on how to fix it?
> >
> >Thanks
> >
> >
> >

Thread options