CODEWITHSUNDEEP
sqlsql-server-2005
chobo2
chobo2

Reputation: 85845

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

I am trying to put a default value of "1/1/0001 12:00:00 AM" in but first it does not allow me.

So I then tried to put "1/1/0001" but when I test it. It gets changed to (((1)/(1))/(1)) and when I try out the default value it gives back "01/02/1900 12:00:00 AM" I am not sure why.

Upvotes: 0

Views: 11248

Answers (3)

Thomas
Thomas

Reputation: 64664

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

Upvotes: 1

leoinfo
leoinfo

Reputation: 8215

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.

Upvotes: 2

Gabe
Gabe

Reputation: 86768

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.

Upvotes: 2

Related Questions