problem with comparing date column using dateadd and getdate

dsunbury

Hi everyone,

I run a comparison of two databases every day. I usually only check from a certain date (from yesterday) as usually there will be new entries from yesterday and a couple of changes only.

So in the date column we have a where clause used in SQL Data Compare, for example:

DateTimeStamp >= CONVERT(DATETIME, '2007-09-01 00:00:00', 102)

so this will only compare anything with a date on or after the 1st of Sep.

Now, instead of changing this every few days I though we could use the following instead so that the system would automatically check from a few days ago:

DateTimeStamp >= CONVERT(DATETIME, DATEADD(day, - 2, GETDATE()), 102)

I have also tried the following (not converting to datetime):

DateTimeStamp >= DATEADD(day, - 2, GETDATE())

Neither works - I get strange results in the results window, showing that I have some data missing on the destination database when this is not the case.

Shouldn't the where clauses above work?

Thanks for any help.

richardjm

That should work fine. Although what you may be getting is some date synch problems between the two servers you are comparing, if the times are out slightly the GETDATE() will return slightly different values therefore the records may differ slightly.

Possibly it may be worth rounding the date to the beginning of the current day and see how you get on with that?

HTH

dsunbury

Hey Richard, your suggestion has prompted me to think about the time zones.

My local server is in US date format, but I am in Australia - the server I am trying to synchronize to is also in US date format, but physically situated in the US. So the getdate() function will give different results!

Hmmm, not sure how to get around this one. :?

dsunbury

I'll try this on the server in the US:

DateTimeStamp >= CONVERT(DATETIME, DATEADD(hour, - 63, GETDATE()), 102)

and this on the local server:

DateTimeStamp >= CONVERT(DATETIME, DATEADD(day, - 2, GETDATE()), 102)

and see how I go