VBdotnet2005
asked on
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
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
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
order by year desc, order by month desc, order by day desc
Use ORDER BY TimeStamp DESC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Change the date to an integer and order by that.
I'll assume the top part of your query works
Add this
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
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
ASKER
thank you
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 :)
order by [year] desc, [month] desc, [date] desc
or, don't use SQL terms as column names :)
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