Vfp Converting DateTime Problem in MSSql

Hi *.
i have table on MsSqlServer "art" with 2 fields id int, date datetime
and when try to put some value into datetime(), MsSql say go away :(

mSql = "insert into art (id, date) values (2, '20061103141100')"
=sqlExec(nHandle, mSql)

Please give me solution for this problem in Vfp.

p.s. Don't give me example with store procedure, 'coz i use mssql for storage only.

Thx.

Visual FoxPro1

Alexander,

Send the date to SQL Server like this:

mSql = "insert into art (id, date) values (2, '2006-11-03 14:11:00')"

As always there is at least 3 ways to solve that problem in VFP. My favorite is parameters and VFP does the conversion for you as necessary. ie:

mSQL = "insert into art (id, date) values (2, m.myDate)"
myDate = {^2006/11/10 03:14:11} && plain VFP datetime
SQLExec(m.nHandle, m.mSQL)

Thx David.
I have just one more question.
How to insert empty datetime into sql server.

Thx again.

Hi,
Assign .NULL. value to your variable if you are following cetin's approach (parameterized sql), or replace your date value with NULL if you put your value as string into SQL.

lddate = .NULL.
SQLEXEC(m.nhandle, 'INSERT INTO mytable (mydate) VALUES ( lddate)')

or

SQLEXEC(m.nhandle, 'INSERT INTO mytable (mydate) VALUES (NULL)')

Note: do not use empty string '' or {} as your date value because it will cause SQL Server to update your date field with date value 01/01/1900.

HTH

Alexandar,

You need to make the column nullable in SQL and set it the value to null.