I have columns that are of datetype, that I use for things like created, lastmodified etc.
I don't want these columns to allow for nulls, is there a best practise in terms of what I should use for their unused, initialized state?
e.g. should I use whatever value datetime.minvalue is?
The best practice is to use NULL when you want to store an unused date. When you are reading it out you can check for NULL and use DateTime.MinValue if you want.
You should use NULL - this is what it is for; unknown or uninitialized values.
As mentioned in the comments there is no point for making DateCreated nullable. Some people even argue that a good and highly normalized database design should not allow null values at all - see for example this article - and I tend to agree.
My preference is to set a default value of getDate() or getUTCDate() for columns in such circumstances.
For example, CreatedDate should not be NULL otherwise the record should theoretically not exist in the first place?
Subsequently if a record exists then it has been created and the therefore has also been modified and so modified date could also have a default value of getDate().
This is also the approach that is typically used to track record/row changes in a Data Warehouse.
I don't want these columns to allow for nulls, is there a best-practise in terms of what I should use for their unused, initialized state?
Since you don't want to use null, you could use January 1, 1753 0:00:00. This is the minimum possible value for a datetime in SQL Server and unlikely to be an actual value in your data set.
But really, you should use null if you can.
The DateTime.MinValue does not fit inside the datetime data type. You could use the datetime2 data type, but like other recommended, you should use NULL.
I frequently create a column like LastModified or LastLogin and it does default to null.
Seeing a null quickly inform me that the user never modified the data or logged into the system. The NULL value in this case does mean something.
Null doesn't necessarily work that well for indexing or queries (like between). I tend to use 01/01/0001 or 12/31/9999 in places that beg for a comparable date. (datetime2)
If your columns really are Created and LastModified, then why not just initialize them using GetDate() (or better, GetUtcDate())? That's how filesystems initialize the datetime stamps of newly created files.
But, as others have pointed out, there is no reason to be afraid of null. If you really mean "has no applicable value", then it seems null is a perfectly good way of expressing that.
The unused, uninitialized state is exactly what NULL is designed to represent. You should allow NULL where it's appropriate (eg., in the TERMINATIONDATE column if something hasn't been terminated), and use proper values where NULL isn't (like for DATECREATED when a row is added).
Using nonsense values for NULL dates is confusing to users. "How can the terminated date be in 2099? We're not IN 2099 yet!", or "How can the termination date be in 1732? We didn't exist then!"
Uninitialized means exactly that - uninitialized. It doesn't mean "initialized, but with a bogus value".
I totally agree with the NULL/GETDATE() answers.
As a general best practice, I would avoid storing special datetime values anywhere unless you are prepared to wrap them regularly. Because you end up with having to fix logic to add thresholds when DATEDIFFs start to come out with thousands of months difference, etc.
The rule I go with for dates is never a special datetime value and NOT NULL (by constraint/table definition) unless there is a need for a special reserved date value, then I try to use NULL. If I need multiple reserved dates, I look at individual flag columns with NULL for the datetime instead or joining to a date dimension instead of a datetime column (particularly in warehousing).
In your case with DateCreated and DateModified, I initialize both to GETDATE() with column defaults. DateModified typically gets refreshed in the UPDATE trigger.
I would not put a default date in that was anything except the date the record was entered (and then only if it made sense to do so as in a date_created field). For instance if you use a data not likely to be used in the other data, in the first place, it is incorrect data and that will come back to haunt you someday. Someone who doesn't know that you did such a silly thing will someday be looking for the incomplete records and will of course not find any because there is a date. Or suppose you want the records between a cetain begin and end date and records where the process had begun but not ended. It is harder to query this with a fake date inthere than with a null. Suppose you want the records that are ealier than a set date, you will then have to remember to exclude these records or they will show up in your recordsset, same if you want the later records if you use a date like 3000/01/01. So this is just as much work as rememebering to deal with null might be in querying the database only you are far, far less likely to do so. Therefore you will more frequently have wrong results to date-centered queries.
Null is for precisely these situations where you don't know what the value should be at the time of data entry. Never fake up data to avoid using null. It is a very poor practice.