SQL Server datetime displayed on classic ASP page

What is the value of SunIn?

SunIn is datetime and when it's NULL it displays the word Sunday. When it's not null but it has an actual time value, it displays the time, e.g. 13:50
When I update the entry the NULL becomes "1900-01-01 00:00:00.000"
The word Sunday is not displayed anymore, instead it's displayed as 00:00

When I asked the question I meant "How can I display "1900-01-01 00:00:00.000" as Sunday on a classic ASP page.

I tried:

<%If IsNull(rs("SunIn")) or (rs("SunIn")="1900-01-01 00:00:00.000") Then %>
Sunday
<%else%>
<%=FormatDateTime(rs("SunIn"),vbshorttime)%>
<%end if%>

but still "00:00" was displayed instead of the word "Sunday".

I think it's because you are actually comparing a datetime value (SunIn) to a string ("1900-01-01 00:00:00.000").  You will have to convert the string to a date:

<%If IsNull(rs("SunIn")) or (rs("SunIn")=CDate("1900-01-01 00:00:00.000")) Then %>

Select allOpen in new window

Maybe I missed something, but this gave me the following error message:

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'CDate'

I think the problem is that when you update the entry, you may be inserting something other than a NULL in the SunIn field if there is no actual time.   A datetime field will always default to 1900-01-01 00:00:00.000 if an invalid datetime is inserted.

Check your table structure and make sure your SunIn field will accept NULL. Then check your Update statement to make sure you are inserting a NULL when there is no real datetime value.

I was thinking of an alternative method. I've just placed an update button on the page. When users click it, the 1900-01-01 00:00:00.000 will be set back to NULL:

UPDATE ATOBidNew SET SunIn = NULL WHERE SunIn = '1900-01-01 00:00:00.000'

It's not too user friendly, but I can't think of anything better. This is my update page, how could I modify it in order to insert nulls instead of '1900-01-01 00:00:00.000'?

<%
myId = Request.form("ID")
mySunIn = request.form("SunIn")
StrSQL = "Update MyTable SET SunIn= '" & mySunIn & "' Where ID="&myId&""
conn.Execute StrSQL
%>

Thank you so much for your time.
All the best,
Baxtalo

Thank you very much!

You're very welcome!