More synchronised drowning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

More synchronised drowning

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-10-20 : 12:03:18

I believe this is a fairly classic problem so I'm hoping there will be a good answer - however there is of course the obligatory twist.

I have two tables with identical structure. These two have come from Access.

They are supposed to have identical data BUT one of the columns holds dates, in one table these dates are largely US format and in the other they are largely UK format ie. the format I want. In addition to this most of the records should be identical (i.e. same PK has same date but in a different format) but I know that some have different dates (i.e. same PK has different dates) due to a configuration error and I'm not sure which of them are US format and which are UK format

(I've missed off the time part but when the dates match (in different format), the time parts are identical

e.g. Table 1

PK ... MyDate
1 1/1/2005
2 10/1/2005
3 19/1/2005
.
.
.
200 1/12/2005
201 21/1/2005
202 1/2/2005
203 1/21/2005

Table 2

PK ... MyDate
1 1/1/2005
2 1/10/2005
3 1/19/2005
.
.
.
200 12/1/2005
201 20/2/2005
202 3/4/2004
203 10/2/2003

I need to merge this data (20,000 records). Does anyone have any suggestions on how I may be able to sort this mess out?

Many thanks in advance

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

Kristen
Test

22859 Posts

Posted - 2005-10-20 : 15:02:09

You could convert dates which are VALID based on USA format for source A and UK format for Source B.

For dates that did not validate you could try the alternative format, and "flag" those rows as OK if the [fall back] date format worked.

Then you could "allow through" anything where the converted Date/Time was the same for Source A and Source B.

For the rest you could (perhaps) treat them as Valid if ONE OF Source A and Source B had a valid date

Then you are stuck with sorting out the remainder - which hopefully is a small data set

Of course anything that has one value > 12 and the other value <= 12 is going to treat those as Day / Month respectively. IF all the dates are known to be valid, just their formatting is unknown, then these are particularly "good" values as they are unambiguous.

I suppose, but the same token, that for dates where Day <= 12 AND Month <= 12 then if Source A and Source B have different formats (DD/MM/YY and MM/DD/YY) and the DATETIME converted values match that is also A Good Thing. And maybe you could use that approach to indicate the remainder this is "suspect" and needs more careful testing - if its a small number of rows then "Good" !!

P.S. When are you moving to Suffolk?