ansaurus

Question

How to set default value or binding for datetime to 1/1/0001 12:00:00 AM in SQL Server 2005?

Answer 1

+2 A:

The range of datetime in SQL Server is January 1, 1753, through December 31, 9999. If you want 1/1/0001 as a value, you must use datetime2 in SQL Server 2008.

Gabe 2010-06-20 18:33:32

Ah ok well then I will try then Jan 1, 1753

chobo2 2010-06-20 18:36:35

Ok it changed 1/1/1753 to (((1)/(1))/(1753)) and default value still is 01/02/1900

chobo2 2010-06-20 18:38:15

Maybe you should use "1753-01-01".

Gabe 2010-06-20 18:41:46

Yep. `ALTER TABLE TableName ADD CONSTRAINT ConstraintName DEFAULT '1753-01-01' FOR ColumnName` works for me.

Martin Smith 2010-06-20 18:47:43

It really likes 1900's. I tried 1753-01-01 and it started to make ones with 1904

chobo2 2010-06-20 18:57:18

I guess I will stick with 1900. I just wanted to know why and not all of a sudden it gives something greater than 2010

chobo2 2010-06-20 18:57:46

@chobo2. Enter it with '1753-01-01' in quotes so it doesn't think it is a mathematical expression. (The first attempt being a division the second one being subtraction). It is doing 1753-01-01=1751 then DATEADD(DAY,1751,'1900-01-01') when casting 1751 to a date. Which is why you get `1904-10-18`

Martin Smith 2010-06-20 19:12:20

Answer 2

+1 A:

Try this

CREATE TABLE MyTable (Id INT, aDate DATETIME NOT NULL DEFAULT('1753-01-01'))

Or, if you want to get "fancy", you could try this:

CREATE DEFAULT [dbo].[DefaultDate] AS '1753-01-01'
GO

CREATE TYPE [dbo].[MyDate] FROM [datetime] NOT NULL
GO

EXEC sys.sp_bindefault 
  @defname=N'[dbo].[DefaultDate]', 
  @objname=N'[dbo].[MyDate]' 
GO

CREATE TABLE MyTable (Id INT, aDate [MyDate] NOT NULL)
GO

INSERT INTO MyTable (ID) SELECT 1
SELECT * FROM MyTable

[EDIT] Like Martin Smith very well noticed, sp_bindefault is going to be deleted in a future version (see this ). So, use the first solution to have less headaches with the future upgrades.

leoinfo 2010-06-20 19:38:48

sp_bindefault is deprecated.

Martin Smith 2010-06-20 20:00:17

Answer 3

+1 A:

First, DateTime data types can only store a minimum date of '1753-01-01'. For SmallDateTime, the minimum date is '1900-01-01'. It is not an accident that they chose this value. The calendar itself changed in 1752 and thus trying to compare the number of days from say '1701-01-01' to now is problematic using standard date math.

However, in SQL Server 2008, there a new DateTime2 or Date either of which can store a value of 0001-01-01 but it would be mistake to do so for the reason I just mentioned.

Third, trying to use an arbitrary date to represent the absence of a date value is a mistake IMO. This is what I call the "magic value" approach to avoiding nulls. IMO, it greatly complicates the calling code as the calling code now has to know about and check for the magic value instead of a null to know whether to display a blank. Instead, you should return nullable DateTime values (DateTime?) from your LINQ code and pass that null all the way to the presentation tier so that the presentation code can deal with absent values.or

Thomas 2010-06-20 21:34:58

ansaurus

tags:

views:

answers:

How to set default value or binding for datetime to 1/1/0001 12:00:00 AM in SQL Server 2005?

related questions