SQL: smalldatetime and year 1899

Install the newer SQL Server ODBC driver version and it will convert the blank datetime into 1900-01-01 automatically.

Mine SQL Server ODBC driver version is 6.01.7601.17514. (SQLSRV32.DLL  21. 11. 2010) and it does not insert 1899 into smalldatetme but 01.01.1900.

Newer driver from Microsoft: http://www.microsoft.com/en-us/download/details.aspx?id=36434 is also OK.

Are you talking about a foxpro database as a linked server?
As your source is vfp, why not simply update all tables date fields and turn all empty dates to DATE(1900,1,1)?

OPEN DATABASE...
ADBObjects(laTables,"TABLE")
For Each cTable in laTables
  Use (cTABLE) Alias sometable
  For lnI = 1 To AFields(laFields)
     If aFields[lnI,2]="D"
        lcField = laFields[lnI,1]
        Replace All &lcField With DATE(1900,1,1) For EMPTY(&lcField)
     Endif
  Endfor
Endfor

Select allOpen in new window

Well, in reality we prefer to have a NULL for empty date fields.  In the source and destination tables.
However, the target or the destination is SQL and I rather get 1900 inserted in SQL than changing the dates in VFP to 1900 if they are NULL.
At least on the SQL side, if there are any tables that are having DATE sensitive information then I can simply clear the 1900s to NULL on the SQL side.

So, so far the first possible solution is PCelba's option and we are researching that.
Will come back shortly.
thx

JohnE

FYI
I have tried triggers on the SQL side, the AFTER and INSTEAD OF and so far I have been not been abled to capture the 1899 date before it gets inserted therefore the only option that I might have is to wait and see if the VFP programmer agrees with the ODBC drivers.

will keep you posted.

thx

JohnE

Just one note: You should distinguish between EMPTY date and NULL date at FoxPro side.

NULL is always inserted to SQL Server as NULL (if the column allows NULL, of course).
EMPTY value handling depends on the ODBC driver version.

EMPTY date value can be compared to other date values and it also means the lowest date available. Its formal representation is "  /  /  ".

NULL date has pure SQL meaning so it represents "no data" and you cannot handle it as a standard date value.

EMPTY values are in Visual FoxPro since ancient times of FoxBase so the reason are historical and we did not have any other alternative... NULLs vere introduced in VFP 3 in 1995.

So if you need NULLs at SQL Server side you may replace all empty dates by NULLs at FoxPro side. To turn all FoxPro empty dates to 1900.01.01 is also an option BUT this will probably cause problems to your existing FoxPro application and I cannot recommend it. You may test it on a copy of your VFP data.

To create a trigger which will convert the inserted date is also possible but you have to cerate the trigger on all tables involved in the date conversion task...

And the last note - The newest SQL Server ODBC driver has certain problems when sending varchar(max) columns to FoxPro. It creates char(0) columns and Microsoft ignores all requests to fix this incorrect behavior which started in SQL Native Client ver. 10.

The obvious scenario in today's applications using ODBC connection is several connections each one using different ODBC driver...

I second Pavel Celba,

in short: IF you have NULL on the foxpro side, you wouldn't have a problem. If the DBF fields are empty, but also nullable, you can of course also modify my code to NULL all empty dates.

If you can't do anything on the foxpro side, if no VFP is available, then please state so. My solution of course depends on VFP, but will go through each and every date field of a database.

Bye, Olaf.

Reading my post once more I have to clarify one important point. I don't recommend any FoxPro data updates so neither NULLs nor 1900.01.01 should be replaced in live VFP data. The VFP app behavior would probably be unpredictable and error logs will grow rapidly after such update...

Why? All EMPTY() function calls would return unexpected value with 1900.01.01 as a parameter and NULL values also need different processing.

I think JohnE is intelligent enough to do this VFP data modification on a copy / staging Database, only used for the data migration to SQL Server.

Bye, Olaf.

TRIGGERS in SQL:
either the INSTEAD OF or AFTER, as you know SQL creates 2 invisible tables that are only visible to the triggers; the inserted and deleted.  Thus, these 2 tables use the same structure as the parent physical table that the trigger is created for.
If the physical table is using SmallDateTime the trigger will never work either simply because cannot insert into the "inserted" table if the range falls outside 1900-0-1-01 and 2079-12-31.  Therefore, to have that fixed I will have to have a varchar field added next to each smalldatatime field, have the replication process to insert into the varchar field and allow the trigger to perform its task (see attached) .

Speaking of replication; this operation is an on-going process, meaning a replication from VFP to SQL, therefore, every 5 seconds the transactions are being sent to SQL.
The only solution that I see is a possible new ODBC drivers and I don't mind receiving 1900-01-01.  If NULL then that is even better.   I will have wait and hear from the programmer / designer on what he believes may be a better solution.

UPDATING records on VFP side:
that's not going to happen since we are replicating in real-time between vfp and sql.

Therefore, I must so far admit the possible new ODBC is a better solution but we will see.
I will get back soon to update.

JohnE
5-11-2013-2-05-30-PM.png

use dba 
go

drop table names 
create table Names (FirstName nvarchar(32) null, LastName nvarchar(32) null, DOB smalldatetime null, [DOB Text] nvarchar(20) null)
go

creatE trigger dbo.UTR_Names_InsteadOf
on names
instead of insert
as
begin
	declare @Value nvarchar(12)  

	if @@rowcount = 0 return
	set nocount on
	
	if exists (select count(*) from inserted WHERE cast([DOB Text] as datetime2) < '1900-01-01' GROUP BY [DOB Text] HAVING COUNT(*) >=1)  
		insert into names (firstname, lastname, dob, [DOB Text]) select firstname, lastname, NULL as DOB, [dob text] from inserted 
	else
		insert into names select firstname, lastname, [DOB TEXT], [DOB Text] from inserted 
end

GO

insert into names values('john', 'Esraelo', NULL, '1957-09-16')
insert into names values('john', 'McEnroe', NULL, '1899-01-01')
GO

SELECT  [firstname]
      ,[lastname]
      ,[DOB]
	  ,[DOB Text]
  FROM [names]

Select allOpen in new window

good question Perkins.
yes, I have had altered the fields and overloaded the processes with operand/operator error that we found out the VFP was treating the datetime2 as char..
therefore, I had to change them back to smalldatetime.  Date data type is not a bad choice if time is not required.  Then again, I am not a VFP expert and still have not heard back from the developer, perhaps on Monday.

btw: yes, it was accepting the 1899 dates when I had the datatime2 .. also examined that via trigger as a test today..

It was stated already: VFP data are transferred to SQL every 5 seconds.

Trigger is not a solution in this case and the ODBC driver update seems to be the simplest solution here.

pcelba,
yes, I agree with you.  even if the trigger were the answer it would be an overhead to the process.  We will find out soon.

Olaf,
I think the problem is the ODBC drivers not the VFP.  Of course logically speaking you are right this could happen on the VFP side and clean up the dates. However, we are talking about and enterprise system with 400+tables, 3000+ fields, and number of lines of code? well, you know the rest. And, we have only 1 programmer on that system.
So, we need to take the cleanest route.  We will see.

It doesn't matter how many tables and fields there are, both in the aspect of finding all date fields and writing code for them and in the aspect of the amount of data to convert and last not least in the aspect of not touching the DBFs themselves. You won't update the data of the DBFs, but the data you transfer within the query you do to get the data.

From what you say you do a partial transfer of new data every 5 minutes, so this conversion also is only applied to a limited amount.

The company setmics I work for, has made many database migrations, both ongoing or one time.

Pavel was talking about SQL Server ODBC drivers, which you would use from the VFP side to insert data from there into SQL Server, a newer driver wouldn't change that route. And since you didn't object, you don't seem to talk about the vice versa way of using VFP ODBC driver from within SQL Server.

So if the route is VFP to SQL Server, then you can influence the data on the way into SQL Server on the VFP side.

If the route really is grabbing data via ODBC from VFP, there is no newer driver than the VFP6 ODBC driver and a newer driver also wouldn't change the empty dates on it's own. Then you still could query iif(empty(datefield),date(1900,1,1),datefield) as smalldatetime in VFP6 syntax to change all empty dates to 1900-1-1 in the result you insert into the SQL Server table.

Of course one thing is for sure, if no modifcation of data and code is needed, but a newer SQL Server ODBC driver retrieves empty dates as ultimodate 1900-1-1 on it's own, that's the least effort solution. I doubt that, though. I'm not even sure you know what's really done, as you talk of your developers.

Bye, Olaf.

Olaf,
First of all it is not a 5 minute interval replication and it is 5 seconds.   Secondly, I do know what is going on on this end.  I am not a VFP programmer but an SQL data architect / programmer.  There is only 1 person programs the system and must approve any change(s).  Hope this clears thing up a bit for you.

All I care is to get the right data into my data back-end. The data modification is not an option to happen on the vfp side before getting to odbc and getting to me.  

Additionally, you are indicating that the newest ODBC will still not give us what I want therefore we will have to actually prove that and close this ticket when we get the final result. Until then I may update you folks of our activity on this topic periodically.

If the newest ODBC is not a solution nor is the possiblity of data changes on VFP side then that is someting that the business side has to accept the some failing records during the replication.
We will see.

thx

JohnE

The time interval doesn't matter much, you can even have it live, via vfp triggers. Also I don't know, if a later SQL Server odbc driver would help, I just doubt it, as that would only have an importance, if you insert data from the vfp side. You still didn't answer that question.

If you import data from SQL server, you don't use the SQL Server odbc driver at all, therefore a newer version would help you in what way? In no way.

Olaf,

the question says: "The ODBC apparently converts 'blank' dates coming from FoxPro to '1899-12-30' and trying to write that to SQL's SmallDatetime data type field."

which I understand as the VFP to SQL Server export. Why do you still think about the opposite direction?

In that sentence ODBC could mean the SQL ODBC driver or the VFP ODBC driver. If you do an SSIS package for data transfer from DBF to SQL Server the SQL Server would use the VFP ODBC driver and not vice versa.

The situation might also involve a .NET application reading in DBF, writing into SQL Server.

This is what I ask for all the time, who, or which technology or language is doing the data transfer?

Bye, Olaf.

OK, so my understanding was correct accidentally :-)

But if the SSIS package creates Linked server to DBF data then it must use OLE DB provider not ODBC driver.

Another possibility is to create linked DBF table in MS Access (via ODBC) and then link the MS Access from SQL Server. In such case I believe MS Access exports 1899-12-30...

SSIS package is the modern version of DTS (data transformation services) and doesn't involve a linked server, necessarily. You can actually define Data Source object via ODBC and OLEDB, and so VFP ODBC could be involved.

Depending on what the situation is, codewise, the one or other means of fixing this will work with less effort.

If the data is pulled from an SSIS package, using VFPOLEDB Provider surely would be the better thing to do, but even in case of VFP ODBC the query expression iif(empty(datefield),date(1900,1,1),datefield) as smalldatetime  would work and fix all empty dates. In the OLEDB provider you could make use of the EVL() function, too.

In case of data being pushed in by VFP or a .NET application, the update of the SQL Server ODBC driver could help. But you could also program trigger code in a DBC to replicate every DBF change to some SQL Server.

I would suggest some of these solutions in detail, if the situation would be cleared up, but of course I wouldn't want to describe solutions not elegible anyway. The number of tables and fields involved doesn't matter much, I already showed you can programmatically determine all date fields and instead of updating DBFs you can also use this schema data knowledge at this point, to generate code, eg triggers or sql-selects or whatever is needed in the current situation. As the instead of insert trigger does not receive empty dates it's futile to try to solve it on that level, that should be clear by now.

Bye, Olaf.

Team,
I must add that you guys are awesome and enjoyed reading your comments, suggestions and solutions.  We are good here now, between the programmer and myself we have decided to choose a common ground and that is DateTime.  The replication process will be altered to accommodate that and mean while my date fields (90% of them) are set to DateTime.
Thank you again and hope to work with you guys again.
thx

JohnE

Thanks, John!

Using DateTime for all fields was, what we also choose in a data migration using SQL2005, but since there also is the date type now since SQL2008, we would have used that type for 1:1 migration of foxpro date fields.

I actually don't know how a vfp empty date transfers to T-SQL datetime, it isn't necessarily 18991230 again, empty dates just have the quality to be lower than any date, and in ODBC transfer that may always lead to a value just out of the date range of any field type you choose. Way back then we did use an ultimo date, transferred all data in a staging database and used 1900-1-1 for that ultimo date, though we also could have gone for 1753.

If it works, it works, if you still have problems, you know where to find us.

Bye, Olaf.

I disagree slyghtly... :-)
The cleanest solution would be to use NULL value at SQL Server side.

The cleanest solution to migrate empty dates is an ultimo date far before any relevant date of the application, If you migrate it as NULL you also need to update application logic to work with NULL instead of a certain date.

.NULL.<somedate evaluates to .NULL., not .T. or .F. and ultimodate<somedate evaluates to .T. , if the ultimodate is far in the past.

We did have to adjust EMPTY() checks to checks for 1900-1-1, but that was less work for us, so we choose that way, no matter if it's cleaner or not.

A lot of other and new logic is based on NULLable fields. From the datbase theory it's the cleaner approach to nonexistant data, of course.

You have to balabnce pros and cons together with the budget.

Bye, Olaf.

Good conversation.  I personally rather getting the NULLs for no dates. But then again; at this time I am happy that I am not missing nor having records dropping out due to the conversion errors. And, also, I am filtering the youngest (invalid) dates based on the need prior to any operation / calculations, so, we are good for now.
Thank you guys again and hope talking to you guys soon .
ciao

JohnE