Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Location
    California
    Posts
    40

    Unanswered: ORDER BY problem with CONVERT

    Hi,
    I just realized that when I started using the CONVERT function on my dates in my SELECT statement and try to ORDER BY one of the date fields that I convert, the order isn't actually correct. Here's the statement:
    Code:
    $query = "SELECT id, BroSisFirstName, BroSisLastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, BroSisFirstNameLP, BroSisLastNameLP, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),101) AS checkedOutDate, CONVERT(char(10),CONVERT(datetime, CAST(returnedDate as varchar(12))),101) AS returnedDate, CONVERT(char(10),CONVERT(datetime, CAST(lpcheckedOutDate as varchar(12))),101) AS lpcheckedOutDate, CONVERT(char(10),CONVERT(datetime, CAST(lpReturnedDate as varchar(12))),101) AS lpReturnedDate FROM Checkouts WHERE IsClosed < 1 ORDER BY checkedOutDate";
    It's almost as if it's treating the date as a string. Does anybody know why, and how I can correct the issue? I need to use the CONVERT function because I don't want the whole 00:00:00 returned with each date. And I say it's the CONVERT function because if I take off the CONVERT on one of the fields such as checkedOutDate and try to sort by it, it sorts correctly.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    try changing your sql so that your select convert(...) as xyz uses different names...

    eg. if you are converting checked_out_date don't select it as checked_out_date, try selecting it as checked_out_date1.

    Alternatively include the order by date as another field in your query and don't convert it. You will need to select it as something else check order_checked_out_date and then sort on that field.

    I don't know if it will work, but it's worth a try.

  3. #3
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    Well, I tried your first idea, but it didn't work, but don't quite understand your second idea - I'm new to SQL so I was wondering if you could elaborate your explanation? Thanks

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    does this help??

    Code:
    $query = "SELECT id, BroSisFirstName, BroSisLastName, TerrNumber,
     IsCheckedOut, IsLPCheckedOut, BroSisFirstNameLP, ..... etc ..... CAST(lpReturnedDate as varchar(12))),101) AS lpReturnedDate, checkedOutDate as order_date FROM Checkouts WHERE IsClosed < 1 ORDER BY order_date";

  5. #5
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    That did it! Thanks! Sorry for being so lame - like I said before, I'm just a newbie.

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    no worries, it's not always easy to figure out what other people mean when you are swapping emails etc...

    I'm not sure if that is the best way to do things, but I'm glad it worked.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm surprised his original query did not sort correctly, even if he did use the same name as an alias.

    I wonder if it would have worked if he had just fully qualified the field in the SORT statement:

    "ORDER BY Checkouts.checkedOutDate"
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    possible..... not sure to be honest.... it kinda surprised me as well but then (no offense meant) it is a MS product and their behaviour can be a bit perverse.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I wonder if it has something to do with the interface he is using? It doesn't look like he's executing through query analyzer or a stored proc. Perhaps something is doing some independent interpretation of his code before it is sent to the server?

    Hmmm....
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    Well, what you said went over my head blindman, but if it helps, I'm just using PHP on Windows XP Pro w/Apache web server, and I'm executing my query through my PHP scripts.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Now you are over my head.

    It might be worth checking Current Activity Process Info in Enterprise Manager to see exactly what statement is being sent to SQL server.

    Whatever works, I guess!
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Dec 2003
    Location
    California
    Posts
    40
    Ok, I'll look for that and check it out - thanks.

Posting Permissions

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