?
Solved

SQL Server datetime displayed on classic ASP page

Posted on 2012-09-21
9
Medium Priority
?
914 Views
Last Modified: 2012-09-22
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.
0
Comment
Question by:baxtalo
  • 5
  • 4
9 Comments
 
LVL 8

Expert Comment

by:Barry62
ID: 38424091
What is the value of SunIn?
0
 

Author Comment

by:baxtalo
ID: 38424107
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
 
LVL 8

Expert Comment

by:Barry62
ID: 38424178
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:baxtalo
ID: 38424196
Maybe I missed something, but this gave me the following error message:

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'CDate'
0
 
LVL 8

Expert Comment

by:Barry62
ID: 38424228
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
 

Author Comment

by:baxtalo
ID: 38424269
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
 
LVL 8

Accepted Solution

by:
Barry62 earned 2000 total points
ID: 38424295
<%
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
 

Author Closing Comment

by:baxtalo
ID: 38424300
Thank you very much!
0
 
LVL 8

Expert Comment

by:Barry62
ID: 38424794
You're very welcome!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question