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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[tsql] TSQL Default Minimum DateTime

Examples related to tsql