Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ASP to Excel - Date format

Status
Not open for further replies.

spicysudhi

Programmer
Joined
Nov 10, 2003
Messages
575
Location
FR
hi
below is my code
==================================
<%
Response.ContentType ="application/vnd.ms-excel"
set rs = Session("Conn").Execute ("Select to_char(sysdate,'DD-MM-YYYY') dt_fld from dual")
%>
<table>
<tr>
<td align=left><%=rs(0)%>&nbsp;</td>
</tr>
</table>
==================================
Database: oracle

the problem here i am facing is the date i am taking from the query as DD-MM-YYYY and when opened in the excel, it comes as DD/MM/YYYY or others based on locale settings. Is there a way to format while writing/converting as excel to retain the format.

i found a option to put quotes both side but that is not a good solution which could cause problems when users upload the same excel file back to my system with some amendments.

thanks in advance
sudhi
 
Try this:

Code:
<%
Response.ContentType ="application/vnd.ms-excel"
set rs = Session("Conn").Execute ("Select to_char(sysdate,'DD-MM-YYYY') dt_fld from dual")
%>
<table>
<tr>
<td align=left><%="=TEXT(" & rs(0) & "," & chr(34) & "mm-dd-yyyy" & chr(34) & ")" %>&nbsp;</td>
</tr>
</table>

Your date needs to be converted to Microsoft Office date format so you might need to do a nested formula. As follows:

Code:
"=TEXT(DATEVALUE(" & chr(34) & rs(0) & chr(34) & ")," & chr(34) & "dd-mm-yyyy" & chr(34) & ")"

That should get you any formatting you want.

Let me know.

Cassidy
 
thanks for the reply.
with the solution u have given, in Excel its showing as the formula. example =TEXT(13-07-2004,"dd-mm-yyyy")  in the cell.

And you are adding a blank space (&nbsp) to the end while writing. Even with normal rs(0)& "&nbsp;" the output will be in the same format since its a text. but i dont want that space there. if i add space, then it becomes a date and changes the format according to locale.
 
Try it this way:

Code:
<%
Response.ContentType ="application/vnd.ms-excel"
set rs = Session("Conn").Execute ("Select to_char(sysdate,'DD-MM-YYYY') dt_fld from dual")
%>
<table>
<tr>
<td align=left>=TEXT(DATEVALUE(<%=rs(0)%>),"mm-dd-yyyy")</td>
</tr>
</table>

Sorry about the last code. I hade it pulled from Visual Basic COM object that ran client side. Forgot to translate it to server side.

It should still be a Date value just with custom formatting even though your using the TEXT function. If you manually put that in using Excel you can still perform date functions on it just as it would be if you had left it the default date formatting.

Let me know

Cassidy
 
sorry, still no luck with it.

situation is like this.
1. reponse.content type = excel
2. write date (converted to text format say DD-Mon-YYYY)

result
3. displays date in the local settings format, eventhough data written in specific format

the solutn u provided abouve considers it as a date and tries to convert to text. the problem in that is the convertion value for DATEVALUE function itself is depends on locale settings!

adding a space next to the date while writing in ASP displays the date perfect format, but when viewd in WinXP client, it shows that space and also gives problem if user does somethig with date value.

regards,
sudhi
 
The only other way I know to accomplish it is to handle it through a COM object or client side vbscript. This takes the simplicity out of it. Here is the code you would need to achieve that on the client side:

Code:
Dim o

Set o = CreateObject ("Excel.Application")

o.Visible = True
o.Workbooks.Add
o.Sheets("sheet1").Range("A1").Value = "Date"
o.Sheets("sheet1").Range("A1").Numberformat = "mm-dd-yyyy"

That is exactly the same as right clicking on the cell and using the custom format option to format the date. You might have to modify the last statement depending on the version. I have found that for some reason the function changes from excel to excel. Another way possibly to do this is build your spreadsheet via the server and setup a macro to run as soon as the spreadsheet opens to format the date.

It is strange to me that the previous didn't work. I tried it on each version from 97-2003 and it worked perfect. I could extract Day, month, year, julian, and even do functions to determine the hours, distance between dates and such. That aside I know that you can access any feature in excel available using the client side code above.

Sorry I couldn't find a better server side solution.

Cassidy
 
you could pass the value to excel as a string, and retain the formatting, but the problem would reside if you needed to use the date for calculations

[thumbsup2]DreX
aKa - Robert
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top