Vfp Converting DateTime Problem in MSSql  
Author Message
Alexandar Mojsejev





PostPosted: Visual FoxPro General, Vfp Converting DateTime Problem in MSSql Top

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 FoxPro2  
 
 
David Frankenbach





PostPosted: Visual FoxPro General, Vfp Converting DateTime Problem in MSSql Top

Alexander,

Send the date to SQL Server like this:

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



 
 
CetinBasoz





PostPosted: Visual FoxPro General, Vfp Converting DateTime Problem in MSSql Top

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)


 
 
Alexandar Mojsejev





PostPosted: Visual FoxPro General, Vfp Converting DateTime Problem in MSSql Top

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

Thx again.

 
 
chanmy8





PostPosted: Visual FoxPro General, Vfp Converting DateTime Problem in MSSql Top

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

 
 
David Frankenbach





PostPosted: Visual FoxPro General, Vfp Converting DateTime Problem in MSSql Top

Alexandar,

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