Solved

Classic ASP Error when displaying time from database

Posted on 2014-03-07
6
561 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 34

Assisted Solution

by:Paul MacDonald
Paul MacDonald 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
     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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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 52

Accepted Solution

by:
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)
   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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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