You have many options, depending on your database.
The way I do this is to write a SQL query that formats the date before I
even get it into the recordset. If you are using ADO, don't open the
recordset directly from a table (with adoCmdTable), use SQL="SELECT ... FROM
TABLE;" (with adoCmdText). You can use the formatting functions in your
database to format it anyway you like!
I use Access databases, so I can use the VBA functions, which are the same
as in Excel. For example...
"SELECT Format(Now(),"yymmdd") AS SpecialDate FROM Dual;"
"Dual" is a dummy table I always include in my databases. It contains one
line of nothing. I believe I got the idea from Oracle databases where Dual
in a built-in dummy table.
What you can do would be something like this depending on the functions
available in your database's SQL implementation:
"SELECT Format([TheirDate],"dd-mm-yyyy") AS [MyDate], etc FROM
[TheirTable];"
I don't even try to format this in VBScript. It has no useful formatting
commands like VB6, VBA, and, I presume, VB.Net.
A more advanced way to do this (if you write your data into Excel via a
client VBScript written to the browser by an ASP server VBScript) is just to
write it into the cell with the Excel VBA function wrapped around it like
this:
Response.Write "<SCRIPT LANGUAGE=""VBScript"">" & vbCrLf
...
Response.Write "wb.Activesheet.Range(""A1"").Formula = ""=Format(""" &
rsMyRecordset("TheirDate") & """,""dd-mm-yyyy"")" & vbCrLf
...
Response.Write "</SCRIPT>" & vbCrLf
However, writing scripts with scripts can be really confusing.
Good Luck.
— Jim
--
James W. (Jim) Rodgers, P.E., is a Senior Partner with General Consulting
Engineers, LLC, in Atlanta, Georgia.
"Middletree" wrote:
I am trying to display dates in a spreadsheet, but the dates need to be in a
format that will allow them to be sorted in Excel. The datatype in the SQL
Server database is datetime. In this case, I need to display the date only,
not the time. But I don't want to change the datatype in the database
because the time is used in other places.
So what I am doing is pulling it out of the database, then modifying it in
ASP/VBScript by using the datevalue function. This results in values such as
3/31/2006, 4/3/2006, and 4/14/2006. The problem is, the ones with the
single digit date (4/3/2006 in the sample data I just listed) messes up
Excel's sorting capabilities. How can I force the dates to display in a
2-digit date format? It would probably be good to do the same for the month.
BTW, I am assuming that making dates and months into 2 digits will resolve
this issue, but that's all it is: an assumption. Any input on that would be
appreciated.