Options
problem with comparing date column using dateadd and getdate
dsunbury
Posts: 9
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.
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.
Comments
Possibly it may be worth rounding the date to the beginning of the current day and see how you get on with that?
HTH
Project Manager
Red Gate Software Ltd
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. :?
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