How do you deal with DATETIME fields as part of the Primary Key

The short answer to my question is “don’t have them.” Unfortunately, that’s not always a viable answer.

Why is this an issue anyway? SQL Server 2000’s DATETIME field isn’t as precise as the data generated by Windows. What this means is that if you store a DATETIME and then retrieve it and compare it to the original value, the values are likely to be different.

I’ve written scripts in SQL server and in .NET to compare DATETIME fields to within a hundredth of a second and that seems to work, but it’s slower than I’d like and frankly, “klunkier” than I’d like.

Any thoughts? Surely I’m not the only one to have ever run into this…

UPDATE: My office mate came up with a simple and elegant solution. Kind of a “duh“ solution, actually. I feel dumb. :) When you initialize your DATETIME field in your application, have the setter lop off the final milliseconds digit. Of course, then you're only storing up to 1/100th of a second, but that's plenty good enough for what we need it for.

Now playing: Dire Straits - The Man's Too Strong

Posted on Thursday, July 21, 2005 7:59 AM Database Practices | Back to top

Comments on this post: How do you deal with DATETIME fields as part of the Primary Key

# re: How do you deal with DATETIME fields as part of the Primary Key

You could always use DateDiff in the SQL.
ex: DateDiff(ms, DateTime1, DateTime2) > 0

Left by Jon on Jul 26, 2005 8:44 PM

Actually, that was my initial method, along with a BETWEEN.

However, my officemate's solution is better. See above.

Left by Chris J. Breisch on Jul 27, 2005 7:08 PM