Comparing VFP9 datetime to SQL2008r2 datetime

[stanlyn]

Hi,

I need to compare a VFP datetime field to a datetime2(7) (or whatever I need to change it to) SQL field. I'm getting inacurate results with what I've tried...

I need an exact match as I'm using a T-Sql command like this:

update table
set..........
where datetime field comparision......
IF @@ROWCOUNT=0
Insert Into table

I've used combinations of
vfp9 SET CENTURY on/off and SET HOURS TO 24 and 12 commands,
sql cast() and convert() commands

So, how should I setup the sql table datetime datatype that compares exactly to vfp's datetime type?

I'm not doing any math on the field, just using it to see if it exists in the lookup table as shown above...

Thanks, Stanley

 

[Olaf Doschke]

Accuracy of the VFP datetime is seconds, but even within VFP you can get trouble in comparing dates to match exactly. This can already be part of the problem. SQL Server will have some other accuracy.

The best bet to match datetime data therefore is to convert to string on both sides and compare them.

As your logic will insert a new record, if you don't find a certain timestamp, I'd also suggest to not test for a timestamp at all, as VFPs datetime by design only has accuracy up to the second.

If that is sufficient you can convert VFP datetime to a string in the format yyyy-mm-ddThh:mi:ss.mmm via Ttoc(Datetime(),3) and in SQL Server you can get the same string format by convert(char(19),GETDATE(),126) - (See

http://msdn.microsoft.com/en-us/library/ms187928.aspx).

Both are ISO8601 string formats.

Of course repace DateTime() with the datetime field and GetDate() with your sql server datetime field. No need to change to any specific datetime format in SQL Server, convert(,,126) converts them all.

Another obvious solution is to accept a range of one second, which you can get by Dateadd(ms, -500, GETDATE()) and DATEADD(ms, 500, GetDATE())

So eg check "WHERE ?m.ltFoxproDatetime between Dateadd(ms, -500, sqldatetimefield) AND DATEADD(ms, 500, sqldatetimefield)"

Bye, Olaf.

 

[Olaf Doschke]

correction: TTOC(..,3) returns a string in the format yyyy-mm-ddThh:mi:ss, not yyyy-mm-ddThh:mi:ss.mmm. So it's without the milliseconds. ISO 8601 is with milliseconds actually, but VFP doesn't have them. convert(char(19),GETDATE(),126) cuts off the .mmm part, so the expressions given are correct.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

While applying your suggestions, Sql is not returning a record that is actually there. Below is the query, data, and the results:

Query where clause...
where convert(char(19), [entry_date], 126) = '2001-05-16 22:15:31'

Data....
county_ID county_pagekey ENTRY_DATE
KY133 KY13339Y04RJYA 2001-05-16 22:15:04.0000000
KY133 KY13339Y04RJYB 2001-05-16 22:15:18.0000000
KY133 KY13339Y04RJYC 2001-05-16 22:15:31.0000000
KY133 KY13339Y04RJYD 2001-05-16 22:15:46.0000000

Results...
Empty!

I was expecting to see the 3rd item in the result set, but its empty.


Also Olaf, the SQL datetime data was actually copied in from a VFP datetime field, so matching and comparing should be easier??? The logic just checks to see if it already exists on the SQL side...

Thanks, Stanley

 

[Olaf Doschke]

You don't compare with the right format!
Retry with where convert(char(19), [entry_date], 126) = '2001-05-16T22:15:31'

The T is not optional.

Bye, Olaf.

 

[Olaf Doschke]

Within Foxpro, TTOC(Datetime(2001,5,16,22,15,31),3) will also give you that string, including the T.

See?

Bye, Olaf.

 

[JRB-Bldr]

While I have never tried it your way, the way I have done this and it has worked is to use the T-SQL BETWEEN

Such as

SELECT *
FROM MySQLTable
WHERE SQLDate BETWEEN '2001-05-16 22:15:00' and '2001-05-16 22:15:59'

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Yes, this would also work.

I'd just not like to depend on locale settings.

Actually YYYY-MM-DD HH:MM:SS also is a rather canonical not locale specific format, but not using any convert on the sql server field you depend on SQL Server understanding you and either converting the SQLDate field into the same string format or convert the strings to datetimes.

The way SQL Management studio displays table data in a data sheet is not how it is stored. Datetime is a binary value inside the MDF/table and just displayed in a human friendly way. That's also true for numeric data, which you see displayed as decimal numbers, but numbers are stored in binary format. Most any data but strings is not stored in the way it's displayed, and even strings in a way are only the ascii numbers, but that's really a lightweight conversion in comparison to the bits of a float in contrast to a display value in decimals.

For that matter you strongly depend on the default implicit conversion of these strings into datetimes, SQL Server does do implicit conversions to make the comparison, a datetime is not stored as it's string representation.

For that matter I suggested using ISO, as that is international and you see even VFP supports conversion to that format via TToC. It's also used within XML, for example.

Bye, Olaf.

 

[bborissov]

Use parameters instead of building the whole query:

ltTime1 = DATETIME(2012, 4, 1,  1, 23, 15)
ltTime2 = DATETIME(2012, 7, 1, 21, 23, 15)

? SQLEXEC(lnSQLHandler, "SELECT * FROM YourTable WHERE ENTRY_DATE BETWEEN ?m.ltTime1 AND ?m.ltTime2","crsTest")

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[stanlyn]

jrbbldr,

>> WHERE SQLDate BETWEEN '2001-05-16 22:15:00' and '2001-05-16 22:15:59'

No, I don't see this working as it could potentially find a wrong record and update it because there are many many records that scanned in/created within seconds of each other and the sql between is looking for a record that has a resolution of 1 minute... In my case, that could mean up to a hundred records inserted by a single scanner/user...

I need it to match the exact number, string, or datetime value as stored in the vfp datetime field. After all, that is where it came from. Therefore, if the vfp datetime field has '2001-05-16 22:15:31', then I must find this exact same match on the SQL side to determine whether I update or add a new record. If an EXACT match is NOT found, then a new record is added.

Later today, I'll work more with Olaf's original suggestion as I was not able to get it working Friday in the couple hours I gave it. What I was having trouble with was the formatting when it

1. went from VFP to SQL, as in VFP it looks like '04/06/2012 04:29:21 AM' for non-empty datetimes, and ' / / : : AM' for empty ones,

I have a routine that changes empty vfp datetimes to a string before sending it to SQL and it looks like this:
edt = '01/01/0001 00:00:00 AM'

So, does the AM mean anything to sql, as it looks like sql sees everything as 24 hours. Would a similar "set hour to 24" and ''01/01/0001 00:00:00' (without the AM) have the same effect?

2. then SQL shows non empty datetimes as '2003-11-22 02:22:03.0000000', and empty ones as 0001-01-01 00:00:00.0000000,

Notice the dashes in the date portion and a leading year which is very different from what was actually saved, hence the confusion.

What should century and hours be set to on the VFP side?

What should an empty datetime string be set to other than null just before passing it to SQL?

Thanks, Stanley

 

[stanlyn]

Olaf,

>> a datetime is not stored as it's string representation.

So, does that mean that we may never get an exact conversion back to the value that was sent in by VFP?

Stanley

 

[Olaf Doschke]

Yes, Stanley, that's a possible conclusion. But to answer that undoubtedly I would need to look, how sql server really stores datetimes binary. I assume the accuracy is even better than second, and so the reconversion should be precise.

http://www.sql-server-performance.com/2004/datetime-datatype/

This is telling the accuracy is 1/300 second, about 3.33 ms

The VFP help says about the VFP datetime, it also uses 8 bytes, but nothing further is said about how these 8 bytes are composed. I assume it differs from SQL Server.

It wouldn't matter if they were equal, because additional to that there is an intermediat ODBC layer, so there are at least two conversions, if you set m.ltDatetime= Datetime() in VFP and pass it on via ODBC with the ?m.ltDatetime syntax:

First The vfp internal datetime format is converted to an odbc canonical format at the transition from VFP to the ODBC layer and then again, converted to SQL Server format at the transition from ODBC to SQL Server, if not a few more in the transitions through all the ISO/OSI levels of network protocols.

As far as I know ODBC canonical format really is a string.

Reasons enough, a precise reconversion and comparison is not to be expected?

Bye, Olaf.

 

[finsys]

Stanley, did you ever sort this?

A general suggestion for you - you should change the ENTRY_DATE field type in SQL from DATETIME2(7) to DATETIME2(0). There is no point storing 7 decimal places of milliseconds that will always be empty and VFP can never use. It will also reduce storage requirements in SQL.

To answer your question, can you ever get an exact conversion back - yes, absolutely. My main system I support has been doing exactly that for almost 10 years now. SQL will save whatever you send it from VFP (as long as it is in a format it recognises...), the issues come when let SQL itself create dates in DATETIME or DATETIME2 fields with millisecond resolution, DATETIME having 3 decimal place resolution and DATETIME2 up to 10 decimal places.

 

[stanlyn]

Thanks Finsys...

It is good to know that this will work as expected. Are you also saying that the odbc translations will not skew to value, as I need exact matches.

Up until now, I had not resolved it, and this news is wonderful news and dropping the millisecond stuff makes perfect since as well. I'll now go back and re-tackle it.

Thanks, Stanley