• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Get Year Month Date in order

How can I order year month date?
Currently, this is the result.

year  moth date
2015      1        2
2015      1       12
2015      1       5
2015      2       16
2015      1       19
2015      1       26
2015      2       2
2015      2       12
2014      4       4
2014      4       7
2014      5       19

I want the result like this.

year  moth date
2015 2  16
2015 2  12
2015 2  2
2015 1  26
etc


declare @trancode varchar(3)

set @trancode = 'aaa'

select   YEAR(TimeStamp) as [year], MONTH(TimeStamp) as [month], DATEPART(day,timestamp) as [Date]
from tabl1
where trancode =@trancode and YEAR(TimeStamp) >= YEAR(GETDATE()) - 2
group by YEAR(TimeStamp), MONTH(TimeStamp), DATEPART(day,timestamp)
order by  YEAR(TimeStamp) desc
0
VBdotnet2005
Asked:
VBdotnet2005
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
order by   [month] desc

This is the order in TSQL:
1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

But logical query processing order is:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Meaning the alias [month] from SELECT is available for ORDER BY.

Mike
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select convert (cast(year as varchar(4)) + '/' + cast(month as varchar(2)) + '/' + cast(day as varchar(2)), 111) as newDate
order by year desc, order by month desc, order by day desc
0
 
LIONKINGCommented:
Use ORDER BY TimeStamp DESC
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kyle AbrahamsSenior .Net DeveloperCommented:
I was typing to fast . . . correct code below:

select convert (cast(year as varchar(4)) + '/' + cast(month as varchar(2)) + '/' + cast(date as varchar(2)), 111) as newDate
order by year desc, month desc, date desc
0
 
Lee SavidgeCommented:
Change the date to an integer and order by that.

I'll assume the top part of your query works

select   YEAR(TimeStamp) as [year], MONTH(TimeStamp) as [month], DATEPART(day,timestamp) as [Date]
from tabl1
where trancode =@trancode and YEAR(TimeStamp) >= YEAR(GETDATE()) - 2

Open in new window


Add this
order by cast(cast(year(timestamp) as nvarchar(4)) + right('0' + cast(month(timestamp) as nvarchar(2)), 2) + right('0' + cast(day(timestamp) as nvarchar(2)), 2) as int) desc

Open in new window

0
 
VBdotnet2005Author Commented:
thank you
0
 
PortletPaulCommented:
Just as a quick observation, as you are using SQL terms as column names, so you really should protect from possible misinterpretation by using [] or " "

order by [year] desc, [month] desc, [date] desc

or, don't use SQL terms as column names :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now