SQL Server datetime displayed on classic ASP page

Hi Experts,
I'm trying to display the time on my classic ASP page. When the data is NULL the code below displays the word "Sunday".

<%If IsNull(rs("SunIn")) or rs("SunIn")="" Then %>
Sunday
<%else%>
<%=FormatDateTime(rs("SunIn"),vbshorttime)%>
<%end if%>

The problem is that when the entry is updated the NULL becomes "1900-01-01 00:00:00.000" in the database.

I would still like the word "Sunday" displayed.
How could I change the code in order to achieve this? I tried
rs("SunIn")="1900-01-01 00:00:00.000"
but it didn't help.

I would really appreciate your help.
baxtaloAsked:
Who is Participating?
 
Barry62Connect With a Mentor Commented:
<%
myId = Request.form("ID")
mySunIn = request.form("SunIn")
if(isNull(mySunIn)=false) then
   StrSQL = "Update MyTable SET SunIn= '" & mySunIn & "' Where ID="&myId&""
else 
   StrSQL = "Update MyTable SET SunIn= NULL Where ID="&myId&""
end if
conn.Execute StrSQL
%>

Open in new window

0
 
Barry62Commented:
What is the value of SunIn?
0
 
baxtaloAuthor Commented:
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".
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Barry62Commented:
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 %>

Open in new window

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

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'CDate'
0
 
Barry62Commented:
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.
0
 
baxtaloAuthor Commented:
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
0
 
baxtaloAuthor Commented:
Thank you very much!
0
 
Barry62Commented:
You're very welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.