Professional Web Applications Themes

Ordering by date ??? - ASP Database

I have created a DB in Access 2000. The Date Field (clientdate) is currently a MEMO field. The date is being entered as: 10 June 2004 21 January 2005 09 September 2004 How would I display this in Date order ? I have tried changing the field type to Date/Time, and setting it to long date format. I'm using an SQL Query to select Name, Address and Date from the DB (this works).. I've added 'Order By clientdate' this works but the date is displayed as: 10/06/2004 09/09/2004 21/01/2005 Not the long date format ! How do I get it ...

  1. #1

    Default Ordering by date ???

    I have created a DB in Access 2000.

    The Date Field (clientdate) is currently a MEMO field.

    The date is being entered as:

    10 June 2004
    21 January 2005
    09 September 2004

    How would I display this in Date order ?

    I have tried changing the field type to Date/Time, and setting it to long
    date format.

    I'm using an SQL Query to select Name, Address and Date from the DB (this
    works)..

    I've added 'Order By clientdate' this works but the date is displayed as:

    10/06/2004
    09/09/2004
    21/01/2005

    Not the long date format !

    How do I get it to order and display the correct dates.

    I'm displaying it in ASP using <%=rsInfo("clientdate")%>

    Thanks Tom
    TomT Guest

  2. #2

    Default Re: Ordering by date ???

    > The Date Field (clientdate) is currently a MEMO field.

    Uh, why?
    > The date is being entered as:
    >
    > 10 June 2004
    > 21 January 2005
    > 09 September 2004
    Stop letting people enter dates this way!
    > I have tried changing the field type to Date/Time, and setting it to long
    > date format.
    And, what happened?
    > I've added 'Order By clientdate' this works but the date is displayed as:
    Did you try ORDER BY CDATE(ClientDate)?

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)


    Aaron [SQL Server MVP] Guest

  3. #3

    Default Re: Ordering by date ???

    On Mon, 2 Aug 2004 15:40:04 -0400, Aaron [SQL Server MVP] wrote:
    >> The Date Field (clientdate) is currently a MEMO field.
    >
    > Uh, why?
    This was not going to be search or ordered by, and the date is being
    entered from a popup calendar.

    >> The date is being entered as:
    >>
    >> 10 June 2004
    >> 21 January 2005
    >> 09 September 2004
    >
    > Stop letting people enter dates this way!
    This is the format the popup calendar uses, It's also easier to read, than
    10/10/2004

    >
    >> I have tried changing the field type to Date/Time, and setting it to long
    >> date format.
    >
    > And, what happened?
    This seems to work, I can edit or change the date OK, but when I display it
    I get 10/10/04 not 10 October 2004
    >
    >> I've added 'Order By clientdate' this works but the date is displayed as:
    >
    > Did you try ORDER BY CDATE(ClientDate)?
    ?? what does that do ?

    Thanks
    TomT Guest

  4. #4

    Default Re: Ordering by date ???

    > This was not going to be search or ordered by, and the date is being
    > entered from a popup calendar.
    You should start using a date type column, because obviously that's what
    you're storing and how you're using it.

    Then, you don't have to worry about the storage format. You pass in
    YYYY-MM-DD, Access never gets confused about whether 10/12/2004 is December
    10 or October 12, and all of your date calculations and sorting suddenly
    just work.
    > This is the format the popup calendar uses, It's also easier to read, than
    > 10/10/2004
    The point is once you get the date STORED in the database correctly, you can
    display it any way you like.
    > This seems to work, I can edit or change the date OK, but when I display
    it
    > I get 10/10/04 not 10 October 2004
    Did you try <%=formatdatetime(rs("datecolumn"), 1)%> or creating your own
    display function? I think you are missing the point that storage and
    display are two completely separate issues.
    > > Did you try ORDER BY CDATE(ClientDate)?
    > ?? what does that do ?
    CDate() converts the value to a date, so that is how it is sorted, instead
    of what you have, which is a string (so of course 10/02/1954 comes after
    08/02/2004). But for the long run, I strongly recommend revisiting the
    table design before you start littering your queries with kludges that
    shouldn't have to be there in the first place.

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)


    Aaron [SQL Server MVP] Guest

  5. #5

    Default Re: Ordering by date ???

    On Mon, 2 Aug 2004 16:10:41 -0400, Aaron [SQL Server MVP] wrote:


    Thanks for your help, I'm beginning to understand...

    >>> Did you try ORDER BY CDATE(ClientDate)?
    >> ?? what does that do ?
    >
    > CDate() converts the value to a date, so that is how it is sorted, instead
    > of what you have, which is a string (so of course 10/02/1954 comes after
    > 08/02/2004). But for the long run, I strongly recommend revisiting the
    > table design before you start littering your queries with kludges that
    > shouldn't have to be there in the first place.
    I have let the database and fields as they were, and tried 'order by
    CDATE(clientdate)'

    This has resulted in the information, being order by the date.

    Thanks
    TomT Guest

  6. #6

    Default Re: Ordering by date ???

    Use the "order by" clause and format the date using FormatDateTime()

    <%=FormatDateTime(rsInfo("clientdate"), 1)%>

    --

    Regards

    Steven Burn
    Ur I.T. Mate Group
    [url]www.it-mate.co.uk[/url]

    Keeping it FREE!


    "TomT" <tomtadslweb.co.uk> wrote in message
    news:1uodba9fgl3lv.10f58spytruv$.dlg40tude.net...
    > I have created a DB in Access 2000.
    >
    > The Date Field (clientdate) is currently a MEMO field.
    >
    > The date is being entered as:
    >
    > 10 June 2004
    > 21 January 2005
    > 09 September 2004
    >
    > How would I display this in Date order ?
    >
    > I have tried changing the field type to Date/Time, and setting it to long
    > date format.
    >
    > I'm using an SQL Query to select Name, Address and Date from the DB (this
    > works)..
    >
    > I've added 'Order By clientdate' this works but the date is displayed as:
    >
    > 10/06/2004
    > 09/09/2004
    > 21/01/2005
    >
    > Not the long date format !
    >
    > How do I get it to order and display the correct dates.
    >
    > I'm displaying it in ASP using <%=rsInfo("clientdate")%>
    >
    > Thanks Tom

    Steven Burn Guest

  7. #7

    Default Re: Ordering by date ???

    > Use the "order by" clause

    He's already doing that! The problem is that the data is not a date, it is
    just character... so 10 November 1995 comes before 12 December 1992.

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)


    Aaron [SQL Server MVP] Guest

  8. #8

    Default Re: Ordering by date ???

    OK,

    So if I change the field, from Memo to Date/Time, with Long Format.

    And use Order By CDATE(clientdate) Would that be right ???

    Would I get the date outputted as 10 October 2004 or 10/10/04 ?

    How would I fix that ?

    Thanks for your help !




    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    news:exUAsaNeEHA.1656TK2MSFTNGP09.phx.gbl...
    > > Use the "order by" clause
    >
    > He's already doing that! The problem is that the data is not a date, it
    is
    > just character... so 10 November 1995 comes before 12 December 1992.
    >
    > --
    > [url]http://www.aspfaq.com/[/url]
    > (Reverse address to reply.)
    >
    >

    TomT Guest

  9. #9

    Default Re: Ordering by date ???

    Do not use "DATE" as a field name. I used SDATE for the field name.

    SELECT CDate([SDATE]) AS CDATE, NAME, ADDRESS
    FROM TableName
    ORDER BY CDate([SDATE]);


    dlbjr
    Pleading sagacious indoctrination!


    dlbjr Guest

  10. #10

    Default Re: Ordering by date ???

    I'm using 'clientdate' as the field name..

    I'll change the field from MEMO to Date/Time

    I just need to know how to display it as 10 October 2004 not 10/10/04

    Again many thanks



    "dlbjr" <oopsiforgot.com> wrote in message
    news:e$CaT$NeEHA.1048tk2msftngp13.phx.gbl...
    > Do not use "DATE" as a field name. I used SDATE for the field name.
    >
    > SELECT CDate([SDATE]) AS CDATE, NAME, ADDRESS
    > FROM TableName
    > ORDER BY CDate([SDATE]);
    >
    >
    > dlbjr
    > Pleading sagacious indoctrination!
    >
    >

    TomT Guest

  11. #11

    Default Re: Ordering by date ???

    If you make clientdate a date/time column then you can just order by
    clientdate (CDate not needed any more).

    Default display format will depend on system settings. It is usually safest
    to format the date with a function you control. What data format does you
    popup calendar like. Mine uses yyyymmddThh:mm:ss so I use the following to
    format the dates for it:

    Public Function XMLDate(ByVal dtIn)

    Dim strSeconds

    XMLDate = ""

    If Not IsNull(dtIn) Then

    If IsDate(dtIn) Then

    dtIn = CDate(dtIn)

    strSeconds = CStr(Second(dtIn))
    If Len(strSeconds) < 2 Then strSeconds = "0" & strSeconds

    XMLDate = Year(dtIn) & "-" & _
    Right("0" & Month(dtIn), 2) & "-" & _
    Right("0" & Day(dtIn), 2) & _
    "T" & Right("0" & Hour(dtIn), 2) & ":" & _
    Right("0" & Minute(dtIn), 2) & ":" & _
    strSeconds

    End If

    End If

    End Function



    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    [url]www.ielearning.com[/url]


    "TomT" <tomtadslweb.co.uk> wrote in message
    news:410eb955$0$6450$cc9e4d1fnews-text.dial.pipex.com...
    > OK,
    >
    > So if I change the field, from Memo to Date/Time, with Long Format.
    >
    > And use Order By CDATE(clientdate) Would that be right ???
    >
    > Would I get the date outputted as 10 October 2004 or 10/10/04 ?
    >
    > How would I fix that ?
    >
    > Thanks for your help !
    >
    >
    >
    >
    > "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    > news:exUAsaNeEHA.1656TK2MSFTNGP09.phx.gbl...
    > > > Use the "order by" clause
    > >
    > > He's already doing that! The problem is that the data is not a date, it
    > is
    > > just character... so 10 November 1995 comes before 12 December 1992.
    > >
    > > --
    > > [url]http://www.aspfaq.com/[/url]
    > > (Reverse address to reply.)
    > >
    > >
    >
    >

    Mark Schupp Guest

  12. #12

    Default Re: Ordering by date ???

    > I just need to know how to display it as 10 October 2004 not 10/10/04

    Once again. Your display has ABSOLUTELY NOTHING to do with the storage.
    Let me repeat: ABSOLUTELY NOTHING!!!!!! In case it's not clear, storage IS
    NOT equal to presentation! Once you get the *date* out of the database, you
    can format it in any way you like. When you're inserting it into HTML, it
    is no longer a date, it is just a string. But you can take advantage of
    date functions since it LOOKS like a string to VBScript.

    You can see some examples here, though not the *exact* format you want:
    [url]http://www.aspfaq.com/2313[/url]

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)


    Aaron [SQL Server MVP] Guest

  13. #13

    Default Re: Ordering by date ???

    First off, you are right to get it into time/date format in Access, rather
    than memo.

    Second, now that the data sorts correctly, you have to display it correctly.

    In the ASP page, use the formatdatetime function
    formatdatetime(dateFromRecordset,1)

    Fool around with the numbers till you a display format that you like.

    Since none of the number choices yeild exactly what you asked for, you'll
    have to write a custom little function using
    datepart for month, day and year





    "TomT" <tomtadslweb.co.uk> wrote in message
    news:1uodba9fgl3lv.10f58spytruv$.dlg40tude.net...
    > I have created a DB in Access 2000.
    >
    > The Date Field (clientdate) is currently a MEMO field.
    >
    > The date is being entered as:
    >
    > 10 June 2004
    > 21 January 2005
    > 09 September 2004
    >
    > How would I display this in Date order ?
    >
    > I have tried changing the field type to Date/Time, and setting it to long
    > date format.
    >
    > I'm using an SQL Query to select Name, Address and Date from the DB (this
    > works)..
    >
    > I've added 'Order By clientdate' this works but the date is displayed as:
    >
    > 10/06/2004
    > 09/09/2004
    > 21/01/2005
    >
    > Not the long date format !
    >
    > How do I get it to order and display the correct dates.
    >
    > I'm displaying it in ASP using <%=rsInfo("clientdate")%>
    >
    > Thanks Tom

    Marlo Brandon Guest

  14. #14

    Default Re: Ordering by date ???

    You can change the out put using ASP by putting the date into this statement

    FormatDateTime(rsInfo("clientdate"), 1)

    This will print the date out like

    Tuesday, August 03, 2004





    On Mon, 2 Aug 2004 20:32:55 +0100, TomT <tomtadslweb.co.uk> wrote:
    >I have created a DB in Access 2000.
    >
    >The Date Field (clientdate) is currently a MEMO field.
    >
    >The date is being entered as:
    >
    >10 June 2004
    >21 January 2005
    >09 September 2004
    >
    >How would I display this in Date order ?
    >
    >I have tried changing the field type to Date/Time, and setting it to long
    >date format.
    >
    >I'm using an SQL Query to select Name, Address and Date from the DB (this
    >works)..
    >
    >I've added 'Order By clientdate' this works but the date is displayed as:
    >
    >10/06/2004
    >09/09/2004
    >21/01/2005
    >
    >Not the long date format !
    >
    >How do I get it to order and display the correct dates.
    >
    >I'm displaying it in ASP using <%=rsInfo("clientdate")%>
    >
    >Thanks Tom
    BTR Guest

  15. #15

    Default Re: Ordering by date ???

    BTR wrote:
    > You can change the out put using ASP by putting the date into this
    > statement
    >
    > FormatDateTime(rsInfo("clientdate"), 1)
    >
    > This will print the date out like
    >
    > Tuesday, August 03, 2004
    >
    Only if the computer's regional settings result in this format.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows [MVP] Guest

  16. #16

    Default Re: Ordering by date ???

    On Tue, 3 Aug 2004 00:08:22 -0400, Aaron [SQL Server MVP] wrote:
    >> I just need to know how to display it as 10 October 2004 not 10/10/04
    >
    > Once again. Your display has ABSOLUTELY NOTHING to do with the storage.
    > Let me repeat: ABSOLUTELY NOTHING!!!!!! In case it's not clear, storage IS
    > NOT equal to presentation! Once you get the *date* out of the database, you
    > can format it in any way you like. When you're inserting it into HTML, it
    > is no longer a date, it is just a string. But you can take advantage of
    > date functions since it LOOKS like a string to VBScript.
    >
    > You can see some examples here, though not the *exact* format you want:
    > [url]http://www.aspfaq.com/2313[/url]

    I get what you mean... Honestly...

    The information is held in clientdate, and now looks like this
    dd/mm/yyyy
    05/10/2004

    I'm still struggling to get the HTML to display it as 05 October 2004

    When my PopUp Calendar, write the info into the text box, it displays as 05
    October 2004, until I write it to the DB, then its gets displayed as
    05/10/2004


    Some please help ?

    TomT Guest

  17. #17

    Default Re: Ordering by date ???

    > October 2004, until I write it to the DB, then its gets displayed as
    > 05/10/2004
    In the DB? Why do you care how "its gets displayed" in the DB? You're
    still confusing storage and presentation, I think.

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)


    Aaron [SQL Server MVP] Guest

  18. #18

    Default Re: Ordering by date ???


    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    news:ORajHHYeEHA.332TK2MSFTNGP09.phx.gbl...
    > > October 2004, until I write it to the DB, then its gets displayed as
    > > 05/10/2004
    >
    > In the DB? Why do you care how "its gets displayed" in the DB? You're
    > still confusing storage and presentation, I think.
    >
    Aaron,I don't care about what is in the DB, only what is shown on screen and
    so I can order by the date.

    So forgetting how it is in the DB, how do I get it to display correctly on
    screen ??

    Thanks again



    TomT Guest

  19. #19

    Default Re: Ordering by date ???

    If it is stored as a date internally then create a custom date formatting
    function to get the display format you want:

    function dateout (yourdate)
    dateout = datepart( "d",yourdate) & " "
    select case datepart("m",yourdate)
    case 1:
    dateout = dateout & "January"
    case 2
    dateout = dateout & "February"
    ...
    end select

    dateout = dateout & datepart("yyyy", yourdate )
    end function

    Or you can try to get the default locale settings on the server to match the
    format you want.

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    [url]www.ielearning.com[/url]


    "TomT" <tomtadslweb.co.uk> wrote in message
    news:410ff5ca$0$6446$cc9e4d1fnews-text.dial.pipex.com...
    >
    > "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    > news:ORajHHYeEHA.332TK2MSFTNGP09.phx.gbl...
    > > > October 2004, until I write it to the DB, then its gets displayed as
    > > > 05/10/2004
    > >
    > > In the DB? Why do you care how "its gets displayed" in the DB? You're
    > > still confusing storage and presentation, I think.
    > >
    >
    > Aaron,I don't care about what is in the DB, only what is shown on screen
    and
    > so I can order by the date.
    >
    > So forgetting how it is in the DB, how do I get it to display correctly on
    > screen ??
    >
    > Thanks again
    >
    >
    >

    Mark Schupp Guest

  20. #20

    Default Re: Ordering by date ???

    Hi,

    Can anyone explain this to me please !
    Forgetting how the date is presented on screen..

    The date is being entered into the database as:

    09/08/2004
    This is 9th August 2004

    In one script it is interpreted as 9th August 2004.

    How ever in another its seen as 08 September 2004.

    If I change the format within the database to 2004/08/09, access
    automatically reformats it to 09/08/2004.

    What is the best way to sort the formatting of the date in the database ?
    Once that has been done, I can change the way I view the date in ASP !

    Thanks
    TomT Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. Grouping and ordering
    By miken32 in forum MySQL
    Replies: 7
    Last Post: July 12th, 12:03 AM
  2. Help Ordering Records
    By DogBot in forum Coldfusion Database Access
    Replies: 4
    Last Post: June 9th, 02:07 PM
  3. table ordering
    By patbegg in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 25th, 12:17 AM
  4. Ordering of recordsets
    By Ken Schaefer in forum ASP Database
    Replies: 2
    Last Post: August 20th, 12:45 PM
  5. [PHP] File ordering
    By Lowell Allen in forum PHP Development
    Replies: 1
    Last Post: August 12th, 02:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139