Classic ASP Error when displaying time from database

Posted on 2014-03-07
Last Modified: 2014-03-08
Hi Experts,
I'm trying to display a time entry from my Teradata database. I format it like this:
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.
Question by:romsom
  • 3
  • 2
LVL 33

Assisted Solution

paulmacd earned 250 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
          'display some message
     End If

Author Comment

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.
LVL 33

Expert Comment

ID: 39913700

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

It's been a while since I've worked in classic ASP!
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline


Author Comment

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.
LVL 52

Accepted Solution

Scott Fell,  EE MVE earned 250 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)
   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"  
   response.write "The time is FormatDateTime(rs("Arrival_Time"),vbshorttime) 
end if

Open in new window


Author Closing Comment

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)  


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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now