Default Value

I set up a database using Studio Express. I defined one row to be a date
field with a default value of 9999-12-31. When I go back and look at it it
looks like "(((9999)-(12))-(31))".
When I insert a row into this table and I have not specified a value for
this date field, it show a date of "1927-04-06 00:00:00.000". This does not
resemble anything which was suppose to have been defaulted. What am I doing
wrong?It works goos for me...
Try the below code:-
create table xxxx(i int, j datetime default '9999-12-31')
go
insert into xxxx(i) values(100)
go
select * from xxxx
Thanks
Hari
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:68C415E4-058B-49B6-9A08-AFD78266E306@.microsoft.com...
>I set up a database using Studio Express. I defined one row to be a date
> field with a default value of 9999-12-31. When I go back and look at it
> it
> looks like "(((9999)-(12))-(31))".
> When I insert a row into this table and I have not specified a value for
> this date field, it show a date of "1927-04-06 00:00:00.000". This does
> not
> resemble anything which was suppose to have been defaulted. What am I
> doing
> wrong?|||The result of the following script:
SELECT CAST(9999-12-31 AS datetime)
is 1927-04-06 00:00:00.000 because SQL server takes the result of the
arithmetic expression (9956) and converts it to datetime. The result of:
SELECT CAST('9999-12-31' AS datetime)
is the expected '9999-12-31 00:00:00.000' because SQL parses the string and
properly converts it to datetime. However, many date format strings are
ambiguous and dependent on your dateformat settings. I suggest you specify
dates in 'yyyymmdd' format so that the value is properly interpreted
regardless of your dateformat setting. For example:
ALTER TABLE dbo.Table1
ADD CONSTRAINT DF_Table1_MyDatetime
DEFAULT '99991231' FOR MyDatetime
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:68C415E4-058B-49B6-9A08-AFD78266E306@.microsoft.com...
>I set up a database using Studio Express. I defined one row to be a date
> field with a default value of 9999-12-31. When I go back and look at it
> it
> looks like "(((9999)-(12))-(31))".
> When I insert a row into this table and I have not specified a value for
> this date field, it show a date of "1927-04-06 00:00:00.000". This does
> not
> resemble anything which was suppose to have been defaulted. What am I
> doing
> wrong?