I'm creating an application that is essentially an integrity check between two databases - one is MSSQL and one is an old provider Btrieve. As part of the requirements all columns for every table need to be compared to ensure the data matches. Currently we loop through each table, get the basic count of the table in both DBs, and then delve into the columns. For numeric fields we do a simple SUM, and for text fields we sum up the length of the column for every row. If these match in both DBs, it's a good indicator the data has migrated across correctly.
This all works fine, but I need to develop something similar for datetime fields. Obviously we can't really SUM these fields, so I'm wondering if anyone has ideas on the best way to approach this. I was thinking maybe the seconds since a certain date but the number will be huge.
Any other ideas? Thanks!
The most straightforward answer to me would be to convert the date or datetime fields to integers with the same format. YYYYMMDD or YYYYMMDDHHmmss work just fine as long as your formats use leading zeroes. In SQL Server, you can do something like:
SELECT SUM(CAST(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),DateTimeColumn,120),' ',''),':',''),'-','') AS BIGINT)) .....
Alternately, you can convert them to either the number of days from a given date ('1970-01-01'), or the number of seconds from a given date ('1970-01-01 00:00:00') if you use time.
SELECT SUM(DATEDIFF(DAY,'19700101',DateColumn)) ....
I'm not familiar enough with Btrieve to know what kinds of functions are available for formatting dates, however.