Results 1 to 11 of 11

Thread: ordering days

  1. #1
    Join Date
    May 2004
    Posts
    2

    Unanswered: ordering days

    I have a little query that returns me all the days in a month, but the days are not in the order I need. They come out like so..

    January 10
    January 11
    January 12
    January 13
    January 14
    etc
    January 19
    January 2
    January 20
    January 21
    January 22

    here is my sample code

    select [month] + ' ' + [day] as [Date] from mytable
    where [month] = 'january'
    and [year] = '2004'

    Thanks, Jeff

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select [day]
         , [month] + ' ' + [day] as [Date] 
      from mytable
     where [month] = 'january'
       and [year] = '2004'
    order
        by [day]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    You need to CAST "day" to nvarchar Rudy... you'll get an error about not being able to convert an nvarchar to an int otherwise

    CAST([day] as nvarchar(2))

    And it won't work if you try to pull more then two months (not that he's asking to do that, just my two cents)
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i don't agree

    how do you know [day] isn't numeric??????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    How do you know it's not? heh heh

    You're right though.. it depends on the datatype for "day".
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, well, now that we have that little detail out of the way...

    what about this: order by right('0'+[day],2)

    i think that will work in both cases, although it won't be efficient in either!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Ok, now we're just getting silly
    That which does not kill me postpones the inevitable.

  8. #8
    Join Date
    Oct 2003
    Posts
    706
    Anytime you are working with date/time values, you must represent them using a DateTime field-type; not as discrete fields. If your source table consists of a separate "Month" and "Year" fields, you need to go back to that source table and get that additional field as a DateTime value.

    There is simply no way to "sort" data that has been broken-out in this way. But if the date is available in the proper data type (also...) it is, as it should be, trivial.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sundialsvcs
    Anytime you are working with date/time values, you must represent them using a DateTime field-type; not as discrete fields. If your source table consists of a separate "Month" and "Year" fields, you need to go back to that source table and get that additional field as a DateTime value.
    nope, i flat out disagree

    the giveaway is when you say anytime, and emphasize must, that's a sure sign it's probably wrong

    suppose i want to store birthdates, and i want to allow for storing as much as is known

    if great uncle fritz was born in october of 1873, i want to be able to store 1873 and 10 and maybe leave the day unspecified (me personally, i would use a null, but let's not go there)

    great great great grandad was born in 1811 and that's all we know

    so how do you propose to store this type of date/time data in a DateTime value?

    FYI, in mysql you can store these values, as 1873-10-00 and 1811-00-00
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Reality check for Sundial: Too often coders are stuck with the database schema they have inherited, and they are looking for methods to code around the flaws. These issues also come up frequently when importing data from a remote system.

    And statements like "There is simply no way to "sort" data that has been broken-out in this way." are just demonstrably incorrect.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    May 2004
    Posts
    2

    Thanks

    Adding the following line worked..
    order by right('0'+[day],2)

Posting Permissions

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