[tsql] TSQL Default Minimum DateTime

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.

"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

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

Modified datetime DEFAULT ((0)) 

is perfectly safe and sound and allows more elegant queries than '1753-01-01' and more efficient queries than NULL.

However, since first Modified 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().

Sometimes you inherit brittle code that is already expecting magic values in a lot of places. Everyone is correct, you should use NULL if possible. However, as a shortcut to make sure every reference to that value is the same, I like to put "constants" (for lack of a better name) in SQL in a scaler function and then call that function when I need the value. That way if I ever want to update them all to be something else, I can do so easily. Or if I want to change the default value moving forward, I only have one place to update it.

The following code creates the function and a table using it for the default DateTime value. Then inserts and select from the table without specifying the value for Modified. Then cleans up after itself. I hope this helps.

-- CREATE FUNCTION
CREATE FUNCTION dbo.DateTime_MinValue ( )
RETURNS DATETIME
AS 
    BEGIN
        DECLARE @dateTime_min DATETIME ;
        SET @dateTime_min = '1/1/1753 12:00:00 AM'
        RETURN @dateTime_min ;
    END ;
GO


-- CREATE TABLE USING FUNCTION FOR DEFAULT
CREATE TABLE TestTable
(
  TestTableId INT IDENTITY(1, 1)
                  PRIMARY KEY CLUSTERED ,
  Value VARCHAR(50) ,
  Modified DATETIME DEFAULT dbo.DateTime_MinValue()
) ;


-- INSERT VALUE INTO TABLE
INSERT  INTO TestTable
        ( Value )
VALUES  ( 'Value' ) ;


-- SELECT FROM TABLE
SELECT  TestTableId ,
        VALUE ,
        Modified
FROM    TestTable ;


-- CLEANUP YOUR DB
DROP TABLE TestTable ;
DROP FUNCTION dbo.DateTime_MinValue ;

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...

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'
)

I agree with the sentiment in "don't use magic values". But I would like to point out that there are times when it's legit to resort to such solutions.

There is a price to pay for setting columns nullable: NULLs are not indexable. A query like "get all records that haven't been modified since the start of 2010" includes those that have never been modified. If we use a nullable column we're thus forced to use [modified] < @cutoffDate OR [modified] IS NULL, and this in turn forces the database engine to perform a table scan, since the nulls are not indexed. And this last can be a problem.

In practice, one should go with NULL if this does not introduce a practical, real-world performance penalty. But it can be difficult to know, unless you have some idea what realistic data volumes are today and will be in the so-called forseeable future. You also need to know if there will be a large proportion of the records that have the special value - if so, there's no point in indexing it anyway.

In short, by deafult/rule of thumb one should go for NULL. But if there's a huge number of records, the data is frequently queried, and only a small proportion of the records have the NULL/special value, there could be significant performance gain for locating records based on this information (provided of course one creates the index!) and IMHO this can at times justify the use of "magic" values.