Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Entering a null value into table of type datetime

Author  Topic 

cgseenu
Starting Member

3 Posts

Posted - 2002-04-30 : 04:38:01
Hello,
I am having a problem. I have a form field which captures a date from the user. When left empty, the sql inserts a '1/1/1900' value into the table.
I have the column set as datetime and i want to have the value as null when the user does not enter any date.
I tried to set a default to null, but did not work too.
Any help would be greatly appreciated

Seena



timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-30 : 04:48:05
Are you sure that you're application isn't putting a default in there?

What front-end are you using (VB, Access, ASP)?

DateTime columns with no default will be left as null. You won't need to explicitly declare the default as being null, it will do this anyway.

Tim

Go to Top of Page

Nazim
A custom title

1408 Posts

Posted - 2002-04-30 : 04:50:06
Check to see the value passed from your form to your insert statement.

insert into #testtable(adatevar, bint) values(null,22)

when queried will return
null 22


Sniperd!!!!!!!
--------------------------------------------------------------




Edited by - Nazim on 04/30/2002 04:51:27
Go to Top of Page

cgseenu
Starting Member

3 Posts

Posted - 2002-04-30 : 05:02:34


quote:


I am using ASP as front end. Even though I dont set a default as null, i get SQL server 2000 inserting a value 1/1/1900

Any luck
Are you sure that you're application isn't putting a default in there?

What front-end are you using (VB, Access, ASP)?

DateTime columns with no default will be left as null. You won't need to explicitly declare the default as being null, it will do this anyway.

Tim







Go to Top of Page

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-30 : 05:23:51

In that case, you'll need to do a bit of validation on your page to explicitly pass a null value in your SQL: e.g.

If Not IsDate(datvar) Then
sql = "INSERT INTO table(datefield) VALUES (NULL)"
Else
sql = "INSERT INTO table(datefield) VALUES ('" & datVar & "')"
End If





Go to Top of Page

warmac
Starting Member

1 Post

Posted - 2011-05-26 : 21:44:05
Try

Public Function FixNull(ByVal o As Object) As Object
If IsDBNull(o) Then
Return "NULL"
Else
Return o
End If
End Function

' example of use
UpdateAddress(FixNull(txtdOffDate.Value))

' this vb sub takes this value as a string
' passing it into a stored as part of an SQL string
' in the SQL stored procedure it is declared as datatime variable
' passing the NULL word in sets date to NULL
' if you want more code let me know
Go to Top of Page
   

- Advertisement -