quote:

---

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

---

select 
	[Deleted Month] = datename(month,DeletedDate),
	count(*)
from
	MyTable

quote:

---

Originally posted by khtan

What is your consideration of choosing smalldatetime over datetime ?
Sam: I can't imagine the importance of tracking the deleted date to a finer granularity than SMALLDATETIME. Give me a good reason to use DATETIME.

For me, I will definately go for datetime. 9999-12-31 and definately easier to understand than 65535 or Jun 6 2079.
Sam: This may be a good enough reason to go with DATETIME over SMALLDATETIME. It's easier to recall the MAX date.

On your Deleted Bit & Deleted Date, why you have to phase out the bit. Can't you have both in a table, although there might be an slight overlapping of functionality in there.
Sam: Ancient Chinese Proverb: Man who wear two Seikos never sure what time it is.

---

quote:

---

Originally posted by Michael Valentine Jones

I don't see the point of coding the not deleted date as 'Dec 31 9999'. Why not just let it be NULL?

---

quote:

---

Originally posted by Michael Valentine Jones

Especially when someone writes a query like this, and then wonders why so many of the deletions are in December:

select 
	[Deleted Month] = datename(month,DeletedDate),
	count(*)
from
	MyTable

---

SELECT DATENAME(month, DeletedDate)
  FROM MyTable
  WHERE DeletedDate < 'Dec 31 9999' -- All users who are not deleted

quote:

---

Originally posted by byrmol

One thing that worries me about this design is that it is a partial solution. What is stoppping someone from flipping the BIT/datetime back to "Active"? You would lose 2 data timepoints...

---

quote:

---

Originally posted by byrmol

Do you need to know that something was ever deleted? Ie. A row moves from Active->Deleted->Active. The current design misses this scenario.

---

quote:

---

Originally posted by Kristen

1) Use NULL - no difference between "MyDeleteDate IS NULL" and "MyDeleteDate = 2999/12/31"
Sam: I don't know if I can write:
WHERE MyDeleteDate IS NULL OR MyDeleteDate > @ReportDate -- Requires 2 tests at runtime...

instead of

WHERE MyDeleteDate > @ReportDate -- Ahh! That's much better!

any chance the execution plan would be better on one or the other?

2a) Retain the MyDeletedBit column so that all your existing code will still work
Sam: Yes, but eventually, I'd like to replace all references of MyDeletedBit to use MyDeleteDate. I usually kneejerk away from having the same flag twice anywhere (Ref: the Chinese proverb I posted above?). I'm not sure how or when, but "Murphy's Law" (or is it some other law?) says if I keep both the BIT and DATE that they'll contradict each other some day and I'll be debugging that mess. Then again, I could be over reacting.

2b) Create a trigger that sets MyDeletedDate to GetDate if "MyDeletedBit = 1 AND MyDeletedDate IS NULL" and resets it to NULL if "MyDeletedBit = 0 AND MyDeletedDate IS NOT NULL"
I'll be giving this some consideration. I haven't cast anything in stone.

3) Just add your new report for "Was not deleted at the time in question" and all existing "Deleted this" and "Is this deleted" logic, based on the old MyDeletedBit, will still work just fine!
Sam: Sorry, I wasn't clear, but I've gotta implement this on all reports. In my case, there's a drop-down for historic data... pick the year, do the report. The good news, is it's a migratable solution. I can evolve the reports over time in a non-disruptive way.

---

quote:

---

Originally posted by jsmith8858

I haven't tested this to see if there are any weird efficiency drawbacks (there shouldn't be), but one thing you can do is use a UDF to return the constant of "NotDeleted" as whatever date you want:

---