setting null as default to datetime type fields

setting null as default to datetime type fields

Post by Jon » Sat, 20 Jan 2001 02:34:46



This is probably a simple one, but I haven't guessed at the right solution.  I am
writing to a SQL db from a vb app.  Some of the fields are not required.  Allow
nulls is checked.  But when I try to send a null from my app, it shows up as
1/1/1900.  How do I keep SQL from doing this?  Thanks in advance.

Jon

 
 
 

setting null as default to datetime type fields

Post by lindawi » Sat, 20 Jan 2001 03:02:02


Jon,

Are you sure that you are sending a real database null to SQL Server, and
not a zero-length string?  SQL Server converts an empty string to Jan 1,
1900. For example,

select convert(datetime, null) 'Real Null', convert(datetime, '') 'Empty
String'

Real Null                   Empty String
--------------------------- ---------------------------
NULL                        1900-01-01 00:00:00.000

The same applies to strings that contain all spaces.  If you want to insert
an explicit null in a column, you need to use the keyword NULL in your
insert statement:

    insert into MyTable (ModifyDatetime) values(null)

Linda


This is probably a simple one, but I haven't guessed at the right solution.
I am
writing to a SQL db from a vb app.  Some of the fields are not required.
Allow
nulls is checked.  But when I try to send a null from my app, it shows up as
1/1/1900.  How do I keep SQL from doing this?  Thanks in advance.

Jon

 
 
 

setting null as default to datetime type fields

Post by jon » Sat, 20 Jan 2001 03:33:18


Thanx Linda
-----Original Message-----

Jon,

Are you sure that you are sending a real database null to SQL Server, and
not a zero-length string?  SQL Server converts an empty string to Jan 1,
1900. For example,

select convert(datetime, null) 'Real Null', convert(datetime, '') 'Empty
String'

Real Null                   Empty String
--------------------------- ---------------------------
NULL                        1900-01-01 00:00:00.000

The same applies to strings that contain all spaces.  If you want to insert
an explicit null in a column, you need to use the keyword NULL in your
insert statement:

    insert into MyTable (ModifyDatetime) values(null)

Linda



This is probably a simple one, but I haven't guessed at the right solution.
I am
writing to a SQL db from a vb app.  Some of the fields are not required.
Allow
nulls is checked.  But when I try to send a null from my app, it shows up as
1/1/1900.  How do I keep SQL from doing this?  Thanks in advance.

Jon

.