Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > date formatting

Reply
Thread Tools

date formatting

 
 
Middletree
Guest
Posts: n/a
 
      08-07-2006
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.



 
Reply With Quote
 
 
 
 
Jim Rodgers
Guest
Posts: n/a
 
      08-07-2006
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.
>
>
>
>

 
Reply With Quote
 
 
 
 
Middletree
Guest
Posts: n/a
 
      08-07-2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>>
>>
>>
>>



 
Reply With Quote
 
Middletree
Guest
Posts: n/a
 
      08-07-2006
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.


 
Reply With Quote
 
Dave Anderson
Guest
Posts: n/a
 
      08-07-2006
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.


 
Reply With Quote
 
Middletree
Guest
Posts: n/a
 
      08-08-2006
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




 
Reply With Quote
 
Middletree
Guest
Posts: n/a
 
      08-08-2006
> 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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP General 11 11-08-2003 11:24 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP .Net 1 11-08-2003 09:14 PM
Given a date, how to find the beginning date and ending date of that week Matt C Programming 3 11-08-2003 09:07 PM
Given a date, how to find the beginning date and ending date of that week Matt C++ 2 11-08-2003 08:30 PM



Advertisments