date formatting

[Middletree]

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.

[Jim Rodgers]

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.

[Middletree]

I don't know any way to say this without seeming like an idiot, but I have
been staring at your post for a long time now, and still don't understand
any of it.
"Jim Rodgers" <Ji********@discussions.microsoft.comwrote in message
news:BD**********************************@microsof t.com...

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.

[Middletree]

I think I have narrowed down the problem, but not sure how to fix it.

I am producing a spreadsheet like the one linked at
http://www.middletree.net/get.htm

You'll note that the column called Open date has dates, but each one is
padded with 2 spaces before the characters begin. I am using Trim. Have no
idea why it doesn't get rid of those spaces.

[Dave Anderson]

Middletree wrote:

You'll note that the column called Open date has dates, but each one
is padded with 2 spaces before the characters begin. I am using Trim.
Have no idea why it doesn't get rid of those spaces.

This is why (from your code):

<td valign=top width=10 align=left>&nbsp;
10/12/2005
</td>

You have 6 whitespace characters between the end of the tag opening and the
beginning of the date. I recommend you change it to this:

<td valign=top width=10 align=left>10/12/2005</td>
I have also found it useful to use some of Excel's styles when I want to
prevent coercion:

td, th { mso-number-format:"\@"; }
.Number { mso-number-format:General; }
.Fixed { mso-number-format:Fixed; }


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Middletree]

Unbelievable. HTML 101.

Oh well. This wasn't a waste of a thread, and I was unaware of the Excel
styles. I'll have to check into it.

thanks

[Dave Anderson]

Middletree wrote:

Unbelievable. HTML 101.

I recall going through a similar problem myself a few years ago. Excel likes
to guess your data type, so you precede everything with &nbsp; to force
plain text. Much later, you find out that you don't want simply plain text.

My advice is to create a sample Excel spreadsheet with the data formats you
want, then save as html and dissect. That will give you your style
definitions.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Middletree]

My advice is to create a sample Excel spreadsheet with the data formats

you want, then save as html and dissect. That will give you your style
definitions.

Good idea. Thanks.