Solved

Classic ASP Error when displaying time from database

Posted on 2014-03-07
6
557 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 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL - Rotating Values in SQL 9 65
t-sql split string into multiple rows 7 84
Character matching different date formats for dates between 6 46
SQL NULL vs Blank 26 36
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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