[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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
0
JessyRobinson1234
Asked:
JessyRobinson1234
  • 3
  • 2
  • 2
  • +1
1 Solution
 
CluskittCommented:
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.
0
 
Pratima PharandeCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could order by "three" .... as that format will sort correctly.

please read up this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
JessyRobinson1234Author Commented:
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?
0
 
CluskittCommented:
You can use a format that will sort by date even as a string, like 112 or 120.
0
 
Pratima PharandeCommented:
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
0
 
CluskittCommented:
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.
0
 
JessyRobinson1234Author Commented:
Thank you, works like a charm
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now