SQL default date question 2

[DebbieDavis]

Hello, I have a SQL2000 database with datetime fields in some of the tables. Users have asp forms they add data to the tables with. If the user leaves a date field blank, it defaults to 1/1/1900. Is there a way to not have it automatically enter that date and just leave it null? Thanks.

 

[Genimuse]

You'll need to change your ASP code to insert

NULL

instead of

''

when the user leaves the field blank.

 

[DebbieDavis]

Thanks for the reply. I already did that and it still inserts the default sql date 1/1/1900. I even set the default value for the field in the SQL table itself to null. Neither one of those things work. I made sure it was working period by entering a valid date. It is so I'm still lost. Thanks anyway.

 

[Genimuse]

Hmm. I've had no problem inserting NULLs into date fields with SQL Server 2000. Is it possible that the field doesn't allow nulls? (Though I'd think that would throw an error if you inserted one.)

Also (and I know this is a "is your computer plugged in" kind of question), are you certain that your code is attempting to insert a NULL?

 

[DebbieDavis]

Thanks again. Yes, I doublechecked to make sure it allowed nulls and it does. Here's how I'm doing the code:

if isdate(last_serviced) then
last_serviced=last_serviced
else
last_serviced=null
end if

I tried it with last_serviced="" but that isn't working either. Is there a better way to handle it? Many thanks again!! dd

 

[Genimuse]

How is "last_serviced" being used? Is it being added into the SQL? For example, if you have this right now:

SQL = "INSERT INTO MyTable (MyDate) VALUES ('" & last_serviced & "')"

then the final SQL sent to the database, when last_serviced isn't a valid date, is going to look like this:

INSERT INTO MyTable (MyDate) VALUES ('')

because you're using a literal null, not the string NULL. And because you'd have (in this example) single-quotes around it every time, you can't just send it a string called "NULL".

If instead your code looked like this:

if isdate(last_serviced) then
    last_serviced = "'" & last_serviced & "'"
else
    last_serviced = "NULL"
end if

and you used SQL concatenation like this:

SQL = "INSERT INTO MyTable (MyDate) VALUES (" & last_serviced & ")"

(note the lack of single quotes), then if last_serviced is an actual date, the SQL would look like this:

INSERT INTO MyTable (MyDate) VALUES ('12/18/2004')

and if it's not a valid date, then the SQL would look like this:

INSERT INTO MyTable (MyDate) VALUES (NULL)

which is what you actually need. Not a VBScript Null (which is nothing), but the letters N U L L without any single quotes around them. Make sense?

 

[DebbieDavis]

It makes PERFECT sense, and it worked like a charm. Many, many thanks!!

 

[DreXor]

also in the table properites you can set the default value to getdate() or now()

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!