SQL Server date...help!

[brendalisalowe]

I have a question. If I have a date in a textbox, but then delete it so it is blank. Then I update (save) it. When I open my form again and open that record, the date shows up like this: 1/1/1900

Why is that?

[Danial]

Originally posted by brendalisalowe
I have a question. If I have a date in a textbox, but then delete it so it is blank. Then I update (save) it. When I open my form again and open that record, the date shows up like this: 1/1/1900

Why is that?

How are you passing the Date, in VB when you declare a variable as date, it assigns a intial value of 1/1/1900 so i think thats whats happening.

When you update it try passing Null. How are you updating, stored procedure or Front end SQL Query, also what Database are you using?

[brendalisalowe]

I am updating using Front End SQL and SQL Server. What should I do different?

[Danial]

Originally posted by brendalisalowe
I am updating using Front End SQL and SQL Server. What should I do different?

Have you tried passing null value? See if that works and let me know.

[brendalisalowe]

But how do you pass a null value? All I did was delete the value I had in the textbox so it was blank. Is there a better way to go about it?

[Danial]

Originally posted by brendalisalowe
But how do you pass a null value? All I did was delete the value I had in the textbox so it was blank. Is there a better way to go about it?

e.g
if txtMyDate.text ="" then
sql= "Update MyTable set MyDate=null Where ID=" & ID

Conn.execute (sql)
end if

[techgnome]

No Date = 1/1/1900
Null Date = 12/31/1899

This "arguement" has been hashed to deal more than once in these forums. It's the way MS decided to deal with such dates. Not sure why, but in theory, it's because you can't not have a date, the date "has to exist" it would be like not having time.... but that gets into exestencialism and other etherial thinking.

TG

[vb_dba]

In my ASP pages, I intentionally set my date values to '01/01/1900' if the user didn't enter the date. When I perform my select statement I check if the date = '01/01/1900' and just return an empty string if it does since this is not a date we would use here to signify anything:

Select Case When myDate = '01/01/1900' Then ''
                    When myDate Is Null Then ''
                    Else Convert(varchar(10), myDate, 101) End'myDate'
From myTable
Where ID = @ID

If you consider doing it this way, you MUST convert the date to a character field (varchar, char), otherwise it will still return '01/01/1900'.

[brendalisalowe]

Thanks! It works perfect!