Database Date field printing

[sandyas]

Hi,
I have a table in Pervasive SQL. I am accessing it from ASP and getting certain fileds. I want the date field to be printed in MM/DD/YYYY format, whereas in the database it is stored as YYYYMMDD. How do I get around it.

Select table1.colum1,table1.column2 from table1.


<% while not rs.EOF %>
<tr>
<% For each item in rs.Fields %>
<td nowrap style="font-family:verdana; font-size:8px"><%Response.Write (item.value) %></td>
<% next
rs.MoveNext%>
</tr>
<% wend
rs.close
set rs = nothing

Conn.close
Set conn = nothing
%>

 

[DotNetGnat]

do this at the SQL query level...i am not sure about the Pervasive SQL syntax but you can do this your select query...use convert() or cast() functions if available...

if you cannot do that at the query level...then you can use the MID() or Left() or Right() functions in asp...something like this...

YYYYMMDD

Response.write Mid(mydate,5,2)&"/"&Mid(mydate,7,2)&"/"&Left(mydate,4)

-DNG

 

[theniteowl]

One question, why would you use response.write in this line?

<td nowrap style="font-family:verdana; font-size:8px"><%Response.Write (item.value) %></td>

Why not just use this?

<td nowrap style="font-family:verdana; font-size:8px"><%[COLOR=red]=item.value[/color]%></td>

Response.Write from what I know carries a much greater overhead on the server. Since you already have the value in item.value you can just drop it on the page directly instead of using Response.Write to output it.

I am not criticizing, I am just wondering if there is a benefit to your method that I am not aware of or if there is a limitation to outputing the values in the HTML when working with the item directly. I have not often used For Each loops in ASP and do not think I ever used Response.Write within the loop for output.




It's hard to think outside the box when I'm trapped in a cubicle.
In an increasingly self-focused society it is important to recognize the unselfish actions of others so they will feel encouraged to continue such actions. Please give acknowledgement to those who aid you whether it is waving to the person who let you out in traffic, tha

 

[onpnt]

You know...I had always been under the impression theniteowl has on the Response.Write vs. = method. I've even said exactly the same thing as above. Almost word for word in fact.

So as you can tell I'm bored today

Benchmark test....
Benchmark Results - Sorted By Average Time 
Method Name Average Low High 
Method #1 Response.Write 0.145 0.107 0.180 
Method #2 = 0.145 0.119 0.180 

Full Benchmark Results - Sorted By Average Time 
Method Name Average Low High Run 1 Run 2 Run 3 Run 4 
Method #1 Response.Write 0.145 0.107 0.180 0.162 0.107 0.180 0.129 
Method #2 = 0.145 0.119 0.180 0.180 0.119 0.141 0.139 

Times are averages from 4 runs of 10000 loops for each method.
Total Test Run Time: 2.091797s

That was way too quick to get a difference so I went to 100000 iterations

Benchmark Results - Sorted By Average Time 
Method Name Average Low High 
Method #2 = 1.501 1.428 1.586 
Method #1 Response.Write 1.505 1.416 1.551 


Benchmark Results - UnSorted 
Method Name Average Low High 
Method #1 Response.Write 1.505 1.416 1.551 
Method #2 = 1.501 1.428 1.586 

Full Benchmark Results - Sorted By Average Time 
Method Name Average Low High Run 1 Run 2 Run 3 Run 4 
Method #2 = 1.501 1.428 1.586 1.535 1.586 1.457 1.428 
Method #1 Response.Write 1.505 1.416 1.551 1.416 1.520 1.535 1.551 

Times are averages from 4 runs of 100000 loops for each method.
Total Test Run Time: 20.80859s

Still almost identical in performance. Even with a closing and opening %> in there for the =

I could not find much for supporting documentation but I did find this very good article by the 4guys
Performance Metrics for the ASP Response Object

interesting numbers when I run it and add the <%=”.”%> method

Method 1 Time: 0.170 = %>.<%
Method 2 Time: 0.230 = Response.Write "."
Method 3 Time: 1.111 = .Write "."
Method 4 Time: 0.230 = %><%="."%><%

Anyhow, looks like to close of a race for me to say either is better any more. Maybe I should search my thousands of replies and take it all back



General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[theniteowl]

I had read that Response.Write caused a significant performance hit but do not know how it related to using the = method.
The info I read was talking specifically about not using Response.Write in loops to output a lot of information and to build a string with the information instead and then output it all with a single Response.Write. The latest revisions of IIS may have improved the performance and/or using = may have just as big a performance hit.

I remember early on in ASP they said to limit the number of <% %> blocks used as it made for slow performance but I understand that is not a significant issue these days either.

Going to have to put together a performance best practices list to review when working on projects.


It's hard to think outside the box when I'm trapped in a cubicle.
In an increasingly self-focused society it is important to recognize the unselfish actions of others so they will feel encouraged to continue such actions. Please give acknowledgement to those who aid you whether it is waving to the person who let you out in traffic, tha

 

[Tarwn]

On the original post
Is the field your operating on a datefield in the database? If that is the case then the format your seeing that data in the database is meaningless. It is passed as some sort of DateTime ovalue to the SQL driver that your using to talk ot the database, that driver than converts it into a value understandable by the Recordset object, which then delievers it to your code in a way that is understand in VBScript as a DateTime variable. You can use all of the Date/Time functions on it you want and printing it wihtout using a format function should result in a LongDate format.

My guess is your not seeing that long date format, meaning tat either your data is stored as text or that your database driver doesn't know how to present that data as a date to VBS.

- First thing to try is DNG's first thought. See if you can do the conversion in your SQL statement and let the Database handle it.

- Second thing to try is similar to DNG's second suggestion, but not as innefficient (sorry). Concatenations cost in VBS, especially if your doing a lot of them. You could use the same Mid() and Left() functions that DNG did but feed them into a DateSerial(year,month,day) function call.

As far as formatting is concerned, you can use the FormatDateTime function to output a shortdate (theformat your looking for).

A google on any of these function names and "ASP" should get you a function reference for them for more info on how they work or what values they expect.


On Response.Write vs <%=var%>
Using an = is the same thing as Response.Write, it's just a shorthand method. The performance hits are more likely to come from multiple Response.Write's and/or string concatenation versus a single block of HTML with a single =/Response.Write.
Example:

'2 string concats and a write
Response.Write "<input type=""text"" name=""bob"" value=""" & myVar & """>"

'3 writes
Response.Write "<input type=""text"" name=""bob"" value="""
Response.Write myVar
Response.Write """>"

'1 block of HTML, 1 write
%><input type="text" name="bob" value="<%=myVar%>"><%

I don't remember the efficiency numbers, but at some point it actually becomes more efficient to write code in a block of HTML than it does to Response.Write it. There is some efficiency loss going back and forth alot, but for large chunks of HTML (especially with only an occasional ASP variable output) it's actually better to escape out of the ASP block and dump it all in a large HTML block.


-T