Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Classic ASP Error when displaying time from database

Posted on 2014-03-07
6
Medium Priority
?
567 Views
Last Modified: 2014-03-08
Hi Experts,
I'm trying to display a time entry from my Teradata database. I format it like this:
<%=FormatDateTime(rs("Arrival_Time"),vbshorttime)%>
The page displays it nicely, but when the time entry in the database is
1/1/0001 00:00:00
I get an error on my ASP page:
error '80020009'

How can this problem be solved?
I appreciate your help.
0
Comment
Question by:romsom
  • 3
  • 2
6 Comments
 
LVL 34

Assisted Solution

by:Paul MacDonald
Paul MacDonald earned 750 total points
ID: 39913584
Trap for a date earlier than some value, then skip displaying that date.

    If rs("Arrival_Time") > "1/1/1970"
           'display time
     Else
          'display some message
     End If
0
 

Author Comment

by:romsom
ID: 39913640
I've tried the following, but unfortunately it displayed the same error:

<td><%If rs("Arrival_Time") > "1/1/1970" Then%><%=FormatDateTime(rs("Arrival_Time"),vbshorttime)%><%else%>-<%end if%></td>

All times are displayed on my ASP page, except for the ones that show as 1/1/0001 00:00:00 in the database.
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39913700
Hmm.

Maybe try...
   If CDate(rs("Arrival_Time")) > CDate("1/1/1970")
           'display time
     Else
          'display some message
     End If


It's been a while since I've worked in classic ASP!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:romsom
ID: 39913717
I've tried it, but received the same error. When I run the query in SQL Assistant everything is returned. When I run the same on my classic ASP page, the 1/1/0001 00:00:00 values display the error.
0
 
LVL 54

Accepted Solution

by:
Scott Fell,  EE MVE earned 750 total points
ID: 39913891
Why are you using that as a date?  Non dates should be nulls.

You could try
 

if isdate(rs("Arrival_Time")) then
   response.write "The time is FormatDateTime(rs("Arrival_Time"),vbshorttime)
   else
   response.write "I don't know the time"
end if

if instr("0001",cstr(rs("Arrival_Time"))) then
   response.write "I don't know the time"  
   else
   response.write "The time is FormatDateTime(rs("Arrival_Time"),vbshorttime) 
end if

Open in new window

0
 

Author Closing Comment

by:romsom
ID: 39914859
Thank you very much for your attention. It turned out that I didn't have to change anything on my ASP page. I had to change the SQL and cast Arrival_Time as a char(19)  

CAST(CAST(Arrival_Time AS FORMAT 'HH:MI') AS CHAR(19))

For some reason my ASP page didn't like timestamps but it works fine when casting the result as a char.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

886 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