Solved

Get Year Month Date in order

Posted on 2015-02-17
7
177 Views
Last Modified: 2015-02-17
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
Comment
Question by:VBdotnet2005
7 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40614660
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40614668
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
 
LVL 13

Expert Comment

by:LIONKING
ID: 40614672
Use ORDER BY TimeStamp DESC
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40614675
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40614706
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
 

Author Closing Comment

by:VBdotnet2005
ID: 40614735
thank you
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40615264
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question