Returning a Timestamp from stored procedure to excel

[WaveOut]

My stored procedure looks like this:

FUNCTION Get_Min(start_time timestamp, end_time timestamp, mytag char(24), myvalue integer)
Local mytime timestamp;

mytime = (select min(ts) from history where name like mytag and value > myvalue and ts between start_time and end_time);

return mytime;

end

-------------------------------
I use the following VBA to call the stored procedure and pass the necessary variables:

myDate = ActiveCell.Offset(10 + x, 1 + Y).Value
myDate2 = ActiveCell.Offset(10 + x, 2 + Y).Value
cmd.Parameters.Refresh
cmd("start_time") = Format$(myDate, "dd-mmm-yy hh:mm:ss")
cmd("end_time") = Format$(myDate2, "dd-mmm-yy hh:mm:ss")
cmd("mytag") = "2S-Turb-Spd-Rpm"
cmd("myvalue") = 150

Set ADO_rs = cmd.Execute
If (cmd(0) = myDate) Then
ActiveCell.Offset(10 + x, 8 + Y).Value = "ST already on"
Else
ActiveCell.Offset(10 + x, 8 + Y).Value = Format$(ST_Start_Date, "dd-mmm-yy hh:mm:ss")
End If

-------------
Now here is the problem. The stored procedure does return information back to excel but rather than a timestamp it returns a number. In one case it should of returned 15-Dec-2003 03:44:10 but instead it returned 1247966500.

I've never had a problem returning data from a stored procedure to excel until I tried to return a timestamp. What am I doing wrong?

David

 

[ETID]

Try maybe...


ActiveCell.Offset(10 + x, 8 + Y).Value = Format$("#" + ST_Start_Date +"#" , "dd-mmm-yy hh:mm:ss")


or maybe just

ActiveCell.Offset(10 + x, 8 + Y).Value ="#" + ST_Start_Date +"#"

 

[WaveOut]

Thanks ETID. I gave your suggestion a try. I get an error message that says "Runtime error 13" Type mismatch. I've delcared the variable ST_Start_Date as Variant.

I'm kind of confused as to what the procedure returns. What does the number 1247966500 represent? tenths of a sec or something like that? I'm able to get that value into the variant ST_Start_Date but when I try to format it to a date ... I run into problems.

 

[fredericofonseca]

Try and cast the timestamps as a text format
e.g.
"select cast(min(ts) as char)"

or if using Transact-SQL (SQL Server and Sybase)
"select convert(char(30), timestamp,121)"
Oracle also has it's own convert functions.

I prefer not to use cast for this, as you don't have much control on the format of the date returned.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[WaveOut]

Frederico,
I tried the cast statement, my procedure now looks like this:

---------------------------
FUNCTION Get_Min(start_time timestamp, end_time timestamp, mytag char(24), myvalue integer)
Local mytime char(24);

mytime = (select cast(min(ts) as char) from history where name like mytag and value > myvalue and ts between start_time and end_time);

return mytime;

end
------------------------------------

Now it appears to almost be working but I now get the following error:

Run-Time error -2147467259 (80004005)
Invalid Integer value: "15-Dec-03 03:44:10.0"

Now the date you see there is the correct date to return but for some reason I'm getting that error. The variable (ST_Start_Date) is declared as a Variant. When I get the error it highlights the line:

Set ADO_rs = cmd.Execute

It really does not seem that returning a date from a stored procedure should be this hard

Any other suggestions?

David

 

[fredericofonseca]

It is possible that the problem is with you trying to use a recordset.

There is no need, and it may not work well.

Use the following.
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim my_function_return_value As String

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "demo"
cmd.ActiveConnection = DBConn
cmd.Parameters("@pais1").Value = 1
cmd.Execute
my_function_return_value = cmd.Parameters("@RETURN_VALUE").Value

This was with SQL Server, with a function set as

CREATE FUNCTION demo
(@pais1 numeric(4,0))
RETURNS varchar(24) AS
BEGIN
declare @txt as varchar(24)
select @txt = (select cast(timestamp as char) from paises where pais = @pais1)
return @txt
END


When I tried to do a
set rs = cmd.execute I got other errors that did not relate to what I was doing.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com