Insert Date fields to sql server table 1

[PatMcLaughlin]

Hello Friends, I am trying to insert data into a sql server table. One of the pieced of data (lcprocdate) is in fact a date field in the sql table but a string in foxpro. ("10/19/2013"). I have tried to pass it as a sting, as a date field (ctod). I have tried to send it using the following:

CONVERT(DATETIME, '[COLOR=#EF2929]2013-10-19[/color] 00:00:00',102)

(The red is the lcprocdate variable reformatted for order) all without joy. I need help to get past this hump.

 

[Mike Lewis]

You don't need to do a CONVERT(). Simply pass a string (delimited with single quotes) in the format YYYY-MM-DD.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[PatMcLaughlin]

Thanks Mike... I had the format as MM/DD/YYYY. You are great as always

 

[Olaf Doschke]

I don't know where you got style=102 from. In my reference this means ANSI format yy.mm.dd, so that couldn't work. 101 would be for strings in mm/dd/yyyy format and 103 for dd/mm/yyyy.

As Mike said you can simply pass in a string, some string formats, which are inserted into other field types are auto converted, for dates not only 'YYYY-MM-DD' works, but also 'YYYYMMDD' without any separator. That's even independantly working on the SQL Server Locale or other server environment settings.

And for datetimes you may use 'YYYYMMDD hh:mm:ss'.

It's also not a bad idea to use ?variable instead and don't go throuhg strings. Eg you can SQLPrepare(h,"Insert into table (datefield) Values (?ldDate)"), then set ldDate to a VFP date value and SQLExec(h) will write that and you can repeatedly use the same insert sql by repeatedly calling SQLEXEC(h) just with the handle, to insert varying values. All you need to change is your variable value.

Bye, Olaf.