Passing Dates

[knovak]

I'm trying to pass a date to a SQL7 procedure from VB6. The param in the stored procedure is declared as a datetime. When I pass an empty string (i.e. '') to the procedure, SQL7 wants to put in the value of 1/1/1900. I just want a null value if the date is not passed in.

I also tried using the VB vbNullChar value, but that inserts 12/30/1899 (or something like it).

Anyway, if the date is not required, how do I actually get SQL7 to insert NULL rather than a default date?

Thanks,

Kyle

 

[mdktindia]

Can you please state the problem more clearly?

 

[Guest_imported]

I would handle this in the stored proc by declaring a default for the parameter which makes it optional.

create proc pr_xyz
(@date_var datetime = NULL)

Modify the SQL in VB so the parameter is omitted when the date is to be nulled.

Alternatively, if you don't want to use different SQL statements in VB you could handle null and not null in the stored procedure in two If blocks.

Another way, might be to declare the parameter's data type as sqldatetime. I think this will cause it to translate an empty string to NULL. Not sure if it works in SQL Server 7

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk