Passing Dates to a Stored Procedure

By Bill Graziano on 13 August 2000 | 3 Comments | Tags: Stored Procedures


Pari writes "I take a date from the html form and wish to pass on to the stored procedure where the value is inserted . . .

Pari writes "I take a date from the html form and wish to pass on to the stored procedure where the value is inserted. This is the stmt i give in the asp page:
----------------------------------------
conn.execute ("InsertMain 'id','name','desc','syno','ldate'")

The stored proc is:
---------------------
CREATE PROCEDURE [InsertMain]
@topid varchar(20),
@name varchar(50),
@descr varchar(200),
@syno varchar(150),
@live datetime
AS
Insert into MainTopic(topic_id,Topic_name,description,synopsis,livedate) values(@topid,@name,@descr,@syno,@live)

I get the following error:
-----------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.

PUHLEAZE help me... if this doesnt work i have to work with inline sql stmts which work.... thnx in adv. pari"



SQL Server is pretty pick about dates (and so am I :). Bascially if you get the format just a little bit wrong you will get this type of error. You can find a list of valid date formats in Books Online under the CONVERT statement.

My suggestion would be to pass the date into the parameter a character string and explicitly convert it do a date field. Make @LIVE a VARCHAR(40) field. Then add the following lines:

DECLARE @livedate DATETIME
SELECT @livedate=CONVERT(datetime, @live)
IF @ERROR <> 0 THEN
  /* Do some error processing */
ELSE
  /* Do the insert using @livedate*/


This will explicitly convert from VARCHAR to DATETIME. If there is an error you can trap it and return an appropriate error message. Again, look at the list of valid dates in Books Online and stick with one of those formats and you should be ok.

Discuss this article: 3 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Handling SQL Server Errors (5 April 2010)

Testing with Profiler Custom Events and Database Snapshots (22 June 2009)

Debugging Stored Procedures in Visual Studio 2005 (25 June 2007)

Writing CLR Stored Procedures in C# - Returning Data (Part 2) (23 June 2005)

Writing CLR Stored Procedures in C# - Introduction to C# (Part 1) (6 June 2005)

Managed Data Access Inside SQL Server with ADO.NET and SQLCLR (31 May 2005)

An Evaluation of Stored Procedures for the .NET Developer (22 March 2004)

Run CLR code from a stored procedure (14 October 2003)

Other Recent Forum Posts

Looking for help with If/else statement (1 Reply)

top 10 sales sku (0 Replies)

query sales date + trx time (0 Replies)

Help Needed in Groupby (0 Replies)

compute sum result cannot show in report,how to (2 Replies)

compare today and yesterday time range on sales (2 Replies)

Checkdb results (5 Replies)

sal 1/0 error recovery error (4 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -