SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Entering a null value into table of type datetime		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cgseenu
Starting Member

Australia
3 Posts
Posted - 04/30/2002 :  04:38:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Australia
1242 Posts
Posted - 04/30/2002 :  04:48:05  Show Profile  Visit timmy's Homepage  Reply with Quote
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

United Arab Emirates
1408 Posts
Posted - 04/30/2002 :  04:50:06  Show Profile  Reply with Quote
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

Australia
3 Posts
Posted - 04/30/2002 :  05:02:34  Show Profile  Reply with Quote


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
Flowing Fount of Yak Knowledge

Australia
1242 Posts
Posted - 04/30/2002 :  05:23:51  Show Profile  Visit timmy's Homepage  Reply with Quote

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 Posts
Posted - 05/26/2011 :  21:44:05  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000