SQL Insert Statment with Date Problem

[skaskas]

Hi There,

I wrote a program to convert a VFP file into SQL, all work OK except when the date field is empty.

My Sql Stament will be something like this:
=SQLEXEC(nHandle,"INSERT INTO filename(count,date) VALUES(95,' / / ')"

The error I get is:
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated.

The fields in SQL hase date as smalldatetime

Please advice

Thanks

Sal

 

[chpicker]

Since you're trying to insert a blank date anyway, why not just skip that field?

=SQLEXEC(nHandle,"INSERT INTO filename(count) VALUES(95)&quot

Ian

 

[DSummZZZ]

Did you try?:

=SQLEXEC(nHandle,"INSERT INTO filename(count,date) VALUES(95,{})"

Dave S.

 

[chpicker]

Dave:

Would that work? Is {} the SQL way to specify a blank date? I was under the impression that was a VFP value, but of course I could be mistaken.

Ian

 

[skaskas]

Hi Again,

{} will not work

I can't skip the field because I have multiple date fields that any one of them coupld be empty.

Thanks

Sal

 

[DSummZZZ]

I could have swore that's the way we did it. I was thinking VFP made the conversion to the necessary data type.

I'll have to do some more poking around.

Dave S.

 

[chpicker]

Sal,

If you skip any field, then it will be left blank. Why wouldn't that work for you?

Ian