How to defult date column to null?

Question

Post reply

How to defult date column to null?

jenniferhu46

SSCrazy

Points: 2990
More actions
April 13, 2005 at 9:24 am

#85795

Hi experts,
I am trying to insert a birthday value from my web application to SQL Server 2000 table, but when it inserts a null value and the birthday column will put 1/1/1900, which seems like a default value......
The birthday's datatype is smalldatetime and allow nulls. Is there any way around? Thank you.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

if object_id('test') > 0

drop table test

GO

Create table test

(

TheDate smalldatetime null

)

insert into test default values

select * from test

/*

thedate

null

*/

drop table test

where r u displaying that date so that it appears at 1900/01/01?

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 2

It sounds like in your web application, you are using ADO recordset to add/update records, and you are putting a value of 0 (zero) in the data field. Try to either leave the field not updated, or, if you want to set to null after having a value, set it to vbNull if using ASP, or System.DBNull if using .Net.

Mark

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 3

yes, in my asp.net application, i have put the code to check if the textbox contains null value:

If txtDOB.Text = Nothing Then

Session("DOB") = DBNull.Value Else

Session("DOB") = txtDOB.Text End If

However, I found when the textbox contains null value, on the run time, the value is setting to '#12:00:00AM#" and I am wondering if it's because it's compareing to SQL Server smalldatetime value's minimum value 1/1/1900, so it put 1/1/1900 in the SQL Server.

Any advice is appreciated.

Scott Thornburg Hall of Fame Points: 3343 More actions · Answer 4

Any possibility that txtDOB.Text is actually an empty string, and that the Session variable DOB is then set to ''?

SQL Server converts that to the result you have. FOr example, run this in query analyzer:

select convert(datetime, '')

Scott Thornburg

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hehe, this CAST('' AS DATETIME) is a funny annoying feature of SQL Server.

I've described this here http://www.sql-server-performance.com/fk_datetime2.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

DanKennedy Hall of Fame Points: 3736 More actions · Answer 6

You should be okay if you change your code to

If txtDOB.Text.Trim().Length = 0 Then

Session("DOB") = DBNull.Value

Else

Session("DOB") = txtDOB.Text

End If

Dan
www.firstcs.co.uk

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 7

Thank you for all of your replies. I think what happens is when it's empty string, the retrieved session value from txtDOB.text will turn to #12:00:00AM# before it inserts to database. I will post help on ASP.NET user forum to see if I can get rid of the #12:00:00AM# before it inserts to table.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 8

I think you're problem is trying to store a null in a Session variable. That cannot be done. You must have some code elsewhere that sets the dataset column value to the value of the session value. Put that IF.. Then..Else logic there, testing if the Session value is "", then set the column value to System.DBNull.

Mark