Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Sql - "ORDER BY Date" & Date Format

  • 28-07-2006 10:33am
    #1
    Registered Users, Registered Users 2 Posts: 354 ✭✭


    Can anyone explain why
    "SELECT DATENAME(DD, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(yy, Date) AS Date FROM (SELECT * FROM table1 WHERE xyz) A, (SELECT * FROM table1 WHERE wxy) B WHERE A.ID = B.ID ORDER BY Date"
    
    won't order the dates correctly when
    "SELECT Date FROM table1 ORDER BY Date"
    

    will?

    Am I correct in assuming the format should have no bearing on date Functions? Presumably the subqueries are responsible.

    Edit: Thanks for pointing that out Bonkey. This should make a bit more sense now.


Comments

  • Registered Users, Registered Users 2 Posts: 15,443 ✭✭✭✭bonkey


    Commissar wrote:
    Can anyone explain why "SELECT DATENAME(DD, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(yy, Date) FROM table1 AS Date" won't order the dates correctly when "SELECT Date FROM table1 ORDER BY Date" will?

    I'm guessing its because the first select statement doesn't have an ORDER BY in it.


  • Closed Accounts Posts: 3,357 ✭✭✭Beano


    Let me get this straight. You have a field in a table called date. You select
    DATENAME(DD, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(yy, Date) and alias this as a field also called date. Then you do an order by on the Date field. I'm assuming you think it should be the date field in the table that it uses for the order by? It wont. In this case it will use the field in the select list that isnt in date order. You need to either alias the field in the select list to something other than Date or change what the selected field is returning so that it is in date order. To do this use DATENAME(yy, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(DD, Date) using ddmmyy will not return the values in date order, yymmdd will.


  • Registered Users, Registered Users 2 Posts: 354 ✭✭Commissar


    Thanks Beano. That's certainly moved me in the right direction.


  • Registered Users, Registered Users 2 Posts: 15,443 ✭✭✭✭bonkey


    One other thing....you're using a 2-digit year in there.

    So if you have two dates : Dec 31 1999 and Jan 01 2000, they get re-written as :

    31 12 99 and 01 01 00

    These are then sorted alphabetically (cause they're string values), and cause thats the field that you've specified as your sort.
    The sort order will come back with the 1999 record *after* the 2000 record.

    If you sorted on the original date fields, that couldn't happen.

    Your best bet is possibly to change your ORDER BY to be either a.Date or B.Date, which will reference the field in the source "tables", rather than Date, which will be interpreted as the aliased output field.


  • Registered Users, Registered Users 2 Posts: 15,443 ✭✭✭✭bonkey


    Incidentally, I'd be inclined to get rid of the inner select statements, and write the query more like hte following:

    SELECT DATENAME(DD, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(yy, Date) AS Date
    FROM table1 A, table1 B
    WHERE A.ID = B.ID
    AND xyz (referencing fields as A.fieldname)
    AND wxy (referending fields as B.fieldname)
    ORDER BY Date"

    or

    SELECT DATENAME(DD, Date)+ ' '+ DATENAME(MM, Date)+' '+ DATENAME(yy, Date) AS Date
    FROM table1 A
    INNER JOIN table1 B
    ON A.ID = B.ID
    WHERE xyz (referencing fields as A.fieldname)
    AND wxy (referending fields as B.fieldname)
    ORDER BY Date"

    In both cases, I'd expect to have to change the field inside the DATENAME function calls to either A.Date or B.Date, and presumably the same in the ORDER BY


  • Advertisement
  • Registered Users, Registered Users 2 Posts: 2,781 ✭✭✭amen


    you should avoid columns with names such as Date


Advertisement