asked on

How to order by date ASCENDING for previous years

SELECT distinct
  Convert ( char ( 10), Resolved_Date, 110 ) 'one',
  Convert ( char ( 10), Resolved_Date, 105 ) 'two',
  Convert ( char ( 10), Resolved_Date, 120 ) 'three'
From
  Receiving_log
where Resolved_Date > '01/01/2012'
Order By 
  'one' asc

Open in new window

results in:

01-02-2013      02-01-2013      2013-01-02
01-03-2012      03-01-2012      2012-01-03
01-03-2013      03-01-2013      2013-01-03
01-04-2012      04-01-2012      2012-01-04
01-04-2013      04-01-2013      2013-01-04

i would like to have it like this:

01-04-2013      04-01-2013      2013-01-04
01-03-2013      03-01-2013      2013-01-03
01-02-2013      02-01-2013      2013-01-02
01-03-2012      03-01-2012      2012-01-03
01-04-2012      04-01-2012      2012-01-04

Cluskitt

Instead of ORDER BY 'one' ASC, do ORDER BY ResolvedDate ASC. One is a char field you created, so the order is based on the output as a string, not as a date. Use the date field itself for the correct order.

Pratima

SELECT distinct
Convert ( char ( 10), Resolved_Date, 110 ) 'one',
Convert ( char ( 10), Resolved_Date, 105 ) 'two',
Convert ( char ( 10), Resolved_Date, 120 ) 'three'
From
Receiving_log
where Resolved_Date > '01/01/2012'
Order By
Resolved_Date asc

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JessyRobinson1234

ASKER

Ok but then I have multiple identical as here were many items created in the same day with a different time stamp. The distinct no longer works as desired. How do I get around that?

Cluskitt

You can use a format that will sort by date even as a string, like 112 or 120.

Pratima

Try this

SELECT
Convert ( char ( 10), X.Resolved_Date, 110 ) 'one',
Convert ( char ( 10), X.Resolved_Date, 105 ) 'two',
Convert ( char ( 10), X.Resolved_Date, 120 ) 'three'

From

( Select Resolved_Date
From
Receiving_log
where Resolved_Date > '01/01/2012'
) X

Order By
X.Resolved_Date asc

Cluskitt

If you use ORDER BY three as angelIII proposed, it should work for you. Don't use it between '' though, or sql will order by the string 'three' rather than by the field three.

JessyRobinson1234

ASKER

Thank you, works like a charm