Default value for Datetime column

Hi
I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
My tables have several columns that are defined as datetime fields which
need to default to the empty date that I have chosen.
I type in 01/01/1753 as the default value of the table designer, however
when I insert a new line into any of the tables, the value that gets
inserted is 01/01/1900.
Does anyone know why this is happening? I am assuming that I have some
configuration value set incorrectly.
I realise that I could simply use 1900 as the empty date. I could also
include the field and a value in the insert statement, but that isn't the
point. I'd rather know why the system behaves like this.
Hope someone can help
Thanks in anticipationDATETIME is just that. It is not date or time, it is both. If you set the
default value of 1753-01-01 then it is 1753-01-01 12:00:00.000 AM if no
value is inserted. If you enter a date or a time, *that* value determines
the other part. If you enter a time only in Query Analyzer, you get
1900-01-01 hh:mm:ss, if you do it in Enterprise Manager, you get 1899-12-31
hh:mm:ss. A value you enter overrides the entire value, even if you only
specify a portion. You can't say the date is x if I enter a time of y. If
you want a specific date to go along with your time, enter that date as
well. You should handle this in a stored procedure so that it is
consistent.
http://www.aspfaq.com/2206
http://www.aspfaq.com/2455
"Mark_S" <Mark_S@.nospam.nospam> wrote in message
news:%23$GRC7$1FHA.2056@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
>
> My tables have several columns that are defined as datetime fields which
> need to default to the empty date that I have chosen.
>
> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
>
> Does anyone know why this is happening? I am assuming that I have some
> configuration value set incorrectly.
>
> I realise that I could simply use 1900 as the empty date. I could also
> include the field and a value in the insert statement, but that isn't the
> point. I'd rather know why the system behaves like this.
>
> Hope someone can help
>
> Thanks in anticipation
>|||>> I am using 01/01/1753 to define the "Empty Date" for my datetime fields [sic].
<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. One of the MANY differences in
fields and columns is that a field can be missing or empty while a
column cannot.
Next, the proper format for dates in SQL is '1753-01-01' as per the
ISO-11179 Standards.
That has to do with the internal representation of DATETIME data types
in SQL Server. And Great Britain and its colonies went to the
Gregorian (Common Era) calendar in 1752, nobody started on
'1753-01-01' as far as I can tell.
No, you cannot. That is an integer and not a temporal data element at
all. Temporal data types, like all other SQL data types, can have a
NULL value. You can also add a DEFAULT CURRENT_TIMESTAMP or
DEFAULT '1753-01-01' clause to the DDL.
Exactly what are you trying to do?|||> Exactly what are you trying to do?
Sounds like when he enters a "datetime" value, like '4:30 PM', he wants the
date that goes with that to be 1753-01-01.
A|||Mark_S (Mark_S@.nospam.nospam) writes:
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
Why not use NULL?
There are those who think NULL are evil, but just face it. Any in-band
value that is used to mean "no value" is going to cause problem. If
nothing else, there can be confusion about which that value is.
(Although I will have to admit having used 17530101, in one place in our
system. I had a need to distinguish between "it has always been this
way" (which is 17530101) and "it has never been this way" (which is NULL).)

> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
When you specify a string for a datetime value in SQL Server, all parts
have a default value which is applied. So an empty string, results in
1900-01-01 00.00.00:000.
Since you get this value, you are apparently explicitly specifying a value.
Had you specified a NULL value, you would have gotten a NULL (or a NOT
NULL violation). The default value is only used when nothing at all
is specified.
This script illustrates:
CREATE TABLE datedemo (a int NOT NULL,
d datetime NULL DEFAULT '17530101')
go
INSERT datedemo (a) VALUES (1)
INSERT datedemo (a, d) VALUES (2, NULL)
INSERT datedemo (a, d) VALUES (3, '')
INSERT datedemo (a, d) VALUES (4, '12:24:34')
go
SELECT * FROM datedemo ORDER BY a
go
DROP TABLE datedemo
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I type in 01/01/1753 as the default value of the table designer
Without quotes, this is likely being interpreted as 1 divided by 1 divided
by 1753, which is going to round (dur to integer math) to 0, which is the
base date, 1900-01-01.
If you use a proper and unambiguous format for your date, you can avoid this
kind of thing. (And don't use the table designer.)
CREATE TABLE dbo.MyTable
(
i INT IDENTITY(1,1),
dt DATETIME NOT NULL DEFAULT '17530101'
);
GO
SET NOCOUNT ON;
INSERT dbo.MyTable DEFAULT VALUES;
INSERT dbo.MyTable(dt) SELECT '19000101';
INSERT dbo.MyTable(dt) SELECT '20050101';
INSERT dbo.MyTable(dt)
SELECT CONVERT(DATETIME, 01/01/1753);
GO
SELECT i,dt FROM dbo.MyTable ORDER BY i,dt;
GO
DROP TABLE dbo.MyTable;
GO|||The group is on a rampage for some odd reason.
Make sure you didn't accidentally define the data type as SMALLDATETIME,
which has a lower bound of '1900-01-01 00:00:00.000'.
Sincerely,
Anthony Thomas
"Mark_S" <Mark_S@.nospam.nospam> wrote in message
news:%23$GRC7$1FHA.2056@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> I am using 01/01/1753 to define the "Empty Date" for my datetime fields.
>
> My tables have several columns that are defined as datetime fields which
> need to default to the empty date that I have chosen.
>
> I type in 01/01/1753 as the default value of the table designer, however
> when I insert a new line into any of the tables, the value that gets
> inserted is 01/01/1900.
>
> Does anyone know why this is happening? I am assuming that I have some
> configuration value set incorrectly.
>
> I realise that I could simply use 1900 as the empty date. I could also
> include the field and a value in the insert statement, but that isn't the
> point. I'd rather know why the system behaves like this.
>
> Hope someone can help
>
> Thanks in anticipation
>