date formatting

Discussion in 'ASP General' started by Middletree, Aug 7, 2006.

  1. Middletree

    Middletree Guest

    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, Aug 7, 2006
    #1
    1. Advertising

  2. Middletree

    Jim Rodgers Guest

    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.
    >
    >
    >
    >
     
    Jim Rodgers, Aug 7, 2006
    #2
    1. Advertising

  3. Middletree

    Middletree Guest

    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" <> wrote in message
    news:...
    > 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, Aug 7, 2006
    #3
  4. Middletree

    Middletree Guest

    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.
     
    Middletree, Aug 7, 2006
    #4
  5. 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.
     
    Dave Anderson, Aug 7, 2006
    #5
  6. Middletree

    Middletree Guest

    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
     
    Middletree, Aug 8, 2006
    #6
  7. Middletree

    Middletree Guest

    > 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.
     
    Middletree, Aug 8, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    651
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,378
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    574
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    756
    Richard Heathfield
    Nov 8, 2003
  5. Matt
    Replies:
    11
    Views:
    429
    Aaron Bertrand [MVP]
    Nov 8, 2003
Loading...

Share This Page