Entering a null value into table of type datetime

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

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources