Date format in asp to excel form

Question

This is my original query, when i save my data records to excel file the date format from dd/mm/yyyy to d/m/yyyy, what can i do to change to the excel date format? Hope someone can help me..thanks ya..i hope the date can be dd/mm/yyyy.

strSQL2= "select DISTINCT to_char(PROD,'dd/mm/yyyy') as PROD_FORMATTED, to_char(PRAD,'dd/mm/yyyy') as PRAD_FORMATTED,PROD,PRAD, BRCH,DEPT,SANO,SUBM, to_char(SUBD,'dd/mm/yyyy') as SUBD, STAT, PSFG, TSAM, TLEV, CLEV, GROP, CTLV, CCLV, CRNM, EXFL FROM SANCTH " & _
         "where" & _
         sqlWhere2 & " ((cono,sano) in " & strFilterRole & " or crid='" & SQLEncode(StrCrid) & "')" & _
         "order by SUBD"

Some of the Excel output code;

<td align="left" style="vertical-align:middle"><%=((objRS_Search("PROD_FORMATTED")))%></td>
<td align="left" style="vertical-align:middle"><%=((objRS_Search("PRAD_FORMATTED")))%></td>
<td align="left" style="vertical-align:middle"><%=((objRS_Search("BRCH")))%></td>
<td align="left" style="vertical-align:middle"><%=((objRS_Search("DEPT")))%></td>
<td align="left" style="vertical-align:middle"><%=((objRS_Search("SANO")))%></td>

I get "02/04/2014" in my excel. when i use the code below, it is remove every double quote and the date become 2/4/2014.how can it be 02/04/2014? need anyone's help, thank you very much!

  <td align="left" style="vertical-align:middle"><%=replace((("""" & objRS_Search("PROD_FORMATTED"))) & """",chr(34),"") %></td>

Here you go: http://docs.oracle.com/cd/E11882_01/server.112/e10592/sql_elements004.htm — David Aldridge, Jul 27 '15 at 08:30
@DavidAldridge From this [duplicate question](http://stackoverflow.com/q/31647961/692942) I think the OPs issue is to do with how the date formats once displayed in Excel. — user692942, Jul 27 '15 at 08:32
If you want the date format to be kept in Excel you will need to pass the field as a string. So where you build up your Excel output you will need something like `"""" & YourDateFieldVariable & """"` to make sure it's passed as a string. It would help if you could post your Excel output code not just the Oracle SQL query. — user692942, Jul 27 '15 at 08:40
@Lankymart i am not really sure what u say...what should i change in my query? — Bilis, Jul 27 '15 at 08:43
Changing your query will not help, the important part is where you build the Excel output, you need to make sure that the date field / variable is passed as a string to do that in Classic ASP you need to escape it in quotes `""""` will produce `"` when outputted. — user692942, Jul 27 '15 at 08:45
@Lankymart u mean i have to write " " " " & PROD_FORMATTED& " " " " in the excel output?? — Bilis, Jul 27 '15 at 08:45
@Lankymart <%=((objRS_Search("PROD_FORMATTED")))%> //this are a part ofmy excel output,so how should i put it inside?<%=((objRS_Search(" " " " & PROD_FORMATTED& " " " ")))%> izit like this? — Bilis, Jul 27 '15 at 08:51
Yes, but would help to see the output code so I can advise better. If you building up a simple CSV and then outputting using `Response.ContentType` to set it to Excel then I'd expect something like `row = row & NumberField & ", """" & StringField & """""` etc. It's hard to explain without seeing your code. — user692942, Jul 27 '15 at 08:51
Your not building it up like a CSV so that approach might not work but give it a try. Also add any code into your question using the Edit rather then pasting in comments as it's hard read. — user692942, Jul 27 '15 at 08:54
Doesn't need to be **all** your coding just add the relevant bits to your question. — user692942, Jul 27 '15 at 09:03
The most reliable format for sending dates to Excel, or any spreadsheet, is the ISO standard format of YYYY-MM-DD. I've never seen Excel OpenOffice, or GoogleSheets get it wrong. — David Aldridge, Jul 27 '15 at 09:08
td align="left" style="vertical-align:middle"><%=((objRS_Search("PROD_FORMATTED")))%> <%=((objRS_Search("PRAD_FORMATTED")))%> <%=((objRS_Search("BRCH")))%> <%=((objRS_Search("DEPT")))%> <%=((objRS_Search("SANO")))%> — Bilis, Jul 27 '15 at 09:10
@Lankymart but excel get it as d/m/yyyy , but i nid it to be dd/mm/yyyy — Bilis, Jul 27 '15 at 09:21
@DavidAldridge The issue is the OP wants the date to be formatted a certain way. To appear correctly in Excel the only way I know of is to send it as a string pre-formatted the way required. It's not that the date is passed incorrectly just the format is not what is required by the OP. — user692942, Jul 27 '15 at 09:37
The problem with using a HTML table to trick Excel into creating the worksheet is I'm not sure how you can force a string to make sure your date format is correct. In the past I've done this using a string built CSV then setting the `Response.ContentType` to Excel's mime type and making sure to [correctly pass the BOM](http://stackoverflow.com/a/24681290/692942) if data is not ASCII. Did you try `<%=(("""" & objRS_Search("PROD_FORMATTED"))) & """" %>`? — user692942, Jul 27 '15 at 09:47
@Lankymart yes, i have try, but it is not working..any other solution? — Bilis, Jul 27 '15 at 09:58
@Lankymart hey i try again.it is okay with the date format ady! but yet the quotation is there, like this "02/01/2014" — Bilis, Jul 27 '15 at 10:12
@Lankymart i have try to remove a pair of " " but it's got error .. — Bilis, Jul 27 '15 at 12:53
Surely the presentation of the date format is a matter for the sheet to define. Internally they usually store dates as a number anyway -- "41234" etc.. The usual problem is getting the sheet to unambiguously recognise the data passed in as a date, and also to avoid dd/mm/yyyy vs mm/dd/yyyy formats on import. I'm just saying that in my experience, YYYY-MM-DD is unambiguously interpreted as the correct date value, which can then be formatted for viewing in the desired way using spreadsheet functionality. — David Aldridge, Jul 27 '15 at 14:35

score 2 · Accepted Answer · answered Jul 28 '15 at 04:03

2

<td align="left" style="vertical-align:middle; mso-number-format:\@;"><%=(( objRS_Search("PROD_FORMATTED"))) %></td>

it is done when adding this mso-number-format:\@;

answered Jul 28 '15 at 04:03

Bilis

65
9

Post a new question and I'll try or someone else will. – user692942 Aug 10 '15 at 10:14
If you have reach the question [asking limit](http://stackoverflow.com/help/asking-rate-limited) then you should be looking at why and evaluating your existing questions. Limits are applied when you receive too many down-votes or close requests which commonly is due to questions; lacking adequate information, duplicating existing questions or failure to show basic attempt to address the problem (code examples). My advice would be first go back and assess your existing questions, if you have any marked as duplicate remove them. – user692942 Aug 11 '15 at 09:22
The asking limit is there to educate you and is only temporary, any attempt to bypass this by asking other questions via comments shows a lack of respect for the system so my advice is ride it out and come back armed to write better questions in the near future. – user692942 Aug 11 '15 at 09:26

Date format in asp to excel form

1 Answers1

Linked