Conversion failed because the DateTime data value overflowed the type specified for the DateTime value part in the consumer's buffer

Question

i have a stored procedure in Sybase ASE with date params in it, so when i created a OLE DB Connection and passing the date parameters to the OLE DB Command,And we are mapping to the parameter with OLEDBType.DBTimeStamp type, datetime param type in stored procedure is smalldatetime. Here is the sample code.

OLEDBConnection  con = new OLEDBConnection(connectionstring);
con.open;

OLEDBCommand cmd = new OLEDBCommand(con);
cmd.QueryString = "dbo.job_xb_new"
cmd.QueryType = "Stored Procedure";

cmd.Parameters.Add("@signoff",OLEType.DBTimeStamp);
cmd.Parameters("@signoff").Value = Datetime.now;

cmd.executeNonQuery(); -----------> ERROR HERE

while executing the store-procedure i am receiving the error. "Conversion failed because the DateTime data value overflowed the type specified for the DateTime value part in the consumer's buffer" ? Please help!!!

what's the actual value being passed to the database? – markp-fuso Dec 23 '19 at 20:43 — markp-fuso, Dec 23 '19 at 20:43

score 0 · Answer 1 · answered Jan 28 '20 at 14:17

With the only information given there may be a solution to try.

Change the datatype of your input value to the stored proc to a char/varchar

create procedure dbo.myProc
@inDate varchar(20)
AS
BEGIN
..

END

Perform internal conversion from that with CONVERT before passing to your query.

SET @inDate = CONVERT(datetime,@inDate,[style parameter number])

For troubleshooting, just comment out everything in the procedure and SELECT @inDate first to determine what the data coming in from the OLE DB app looks like. You may be in for a surprise there...

Conversion failed because the DateTime data value overflowed the type specified for the DateTime value part in the consumer's buffer

1 Answers1