TSQL Default Minimum DateTime

Question

Using Transact SQL is there a way to specify a default datetime on a column (in the create table statement) such that the datetime is the minimum possible value for datetime values?

create table atable
(
  atableID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  Modified datetime DEFAULT XXXXX??????
)

Perhaps I should just leave it null.

Answer 1

"Perhaps I should leave it null"

Don't use magic numbers - it's bad practice - if you don't have a value leave it null

Otherwise if you really want a default date - use one of the other techniques posted to set a default date

Answer 2

I think this would work...

create table atable
(
  atableID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  Modified datetime DEFAULT ((0))
)

Edit: This is wrong...The minimum SQL DateTime Value is 1/1/1753. My solution provides a datetime = 1/1/1900 00:00:00. Other answers have the correct minimum date...

Answer 3

As far as I am aware no function exists to return this, you will have to hard set it.

Attempting to cast from values such as 0 to get a minimum date will default to 01-01-1900.

As suggested previously best left set to NULL (and use ISNULL when reading if you need to), or if you are worried about setting it correctly you could even set a trigger on the table to set your modified date on edits.

If you have your heart set on getting the minimum possible date then:

create table atable
(
  atableID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  Modified datetime DEFAULT '1753-01-01'
)

Answer 4

I think your only option here is a constant. With that said - don't use it - stick with nulls instead of bogus dates.

create table atable
(
  atableID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  Modified datetime DEFAULT '1/1/1753'
)

Answer 5

Unless you are doing a DB to track historical times more than a century ago, using

Modified datetime DEFAULT ((0)) 

is perfecly safe and sound and allows more elegant queries than '1753-01-01' and more efficientt queries than NULL.

However, since first Modofied datetime is the time at which the record was inserted, you can use:

Modified datetime NOT NULL DEFAULT (GETUTCDATE())

which avoids the whole issue and makes your inserts easier and safer - as in you don't insert it at all and SQL does the housework :-)

With that in place you can still have elegant and fast queries by using 0 as a practical minimum since it's guranteed to always be lower than any insert-generated GETUTCDATE().