Reputation: 27760
I am trying to retrieve a list of date strings ordered by date like this...
SELECT DISTINCT CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDate
FROM dbo.ProviderProcessGeneratedDate
ORDER BY GeneratedDate
This orders by the varchar that I converted the dates to.
example...
02/01/2008
02/15/2008
02/21/2007
02/23/2007
02/29/2008
I have worked around this using an inline query...
SELECT CONVERT(Varchar(10), a.GeneratedDate, 101) AS GeneratedDate
FROM (SELECT DISTINCT Convert(DATETIME,CONVERT(Varchar(10), GeneratedDate, 101)) AS GeneratedDate
FROM dbo.ProviderProcessGeneratedDate) a
ORDER BY a.GeneratedDate DESC
To get what I really want...
01/11/2008
01/04/2008
12/28/2007
12/21/2007
Is there an easier way? Seems like a lot of work to do something so simple.
Upvotes: 6
Views: 13400
Reputation: 86808
The reason your first query gave a different order from what you wanted is...
- You use the field "GeneratedDate" to create your string
- You then alias that result field to "GeneratedDate"
- You then order by "GeneratedDate" without specifying the table
- So the result field is being used for ordering
The simple fix is mentioned in other answers...
ORDER BY ProviderProcessGeneratedDate.GenerateDate
By specifying the table there is no confusion and you get teh results you wanted.
[aside]
As a rule I always prefix my fields with [table]. to avoid any ambiguity. Especially since I often come back later and add in a join, forcing the ned for the tabel name.
Also, I alias the table names. Not to things like [a], but something meaningful like [Dates]. This shortens the query, but also allows me to change the table being used without having to change other references to it in other parts of the query.
[end of aside]
EDIT:
I've left my previous answer by way of humbling myself. I really should get a home sql server so I can try my answer before I post my answer... ***Apologies*
As the comment states, you may not specify something in the ORDER BY if it's not in the SELECT DISTINCT.
Therefore I would try GROUP BY instead...
SELECT
Convert(DATETIME,CONVERT(Varchar(10), GeneratedDate, 101))
FROM
ProviderProcessGeneratedDate
GROUP BY
GeneratedDate
ORDER BY
GeneratedDate
This assumes GeneratedDate is 1:1 with your CONVERT formula. If, for example, you have a TIME in your GeneratedDate fields, but your Date Format in CONVERT does not; you need to strip out the time from the GeneratedDate Field...
SELECT
Convert(DATETIME,CONVERT(Varchar(10), DATEADD(DAY, DATEDIFF(DAY, 0, GeneratedDate), 0), 101))
FROM
ProviderProcessGeneratedDate
GROUP BY
DATEADD(DAY, DATEDIFF(DAY, 0, GeneratedDate), 0)
ORDER BY
DATEADD(DAY, DATEDIFF(DAY, 0, GeneratedDate), 0)
Upvotes: 2
Reputation: 86172
Use GROUP BY
instead of DISTINCT
:
SELECT
CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDate
FROM
dbo.ProviderProcessGeneratedDate AS BaseDates
GROUP BY
BaseDates.GeneratedDate,
CONVERT(Varchar(10), GeneratedDate, 101)
ORDER BY
BaseDates.GeneratedDate
Upvotes: 0
Reputation: 5186
You can also use CTE's if you're using SQL 2005 or 2008.
your code would look like:
WITH Dates(GeneratedDate) AS
(
SELECT DISTINCT CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDate
FROM dbo.ProviderProcessGeneratedDate
)
SELECT GeneratedDate FROM Dates ORDER BY GeneratedDate
Upvotes: 1
Reputation: 146603
SELECT DISTINCT Convert(DATETIME,CONVERT(Varchar(10), GeneratedDate, 101))
AS GeneratedDate,
A.GeneratedDate OrderByDate
FROM dbo.ProviderProcessGeneratedDate A
Order By A.GeneratedDate Desc
Upvotes: 1
Reputation: 678
Modify the ORDER BY of your original statement to use a sortable date string:
SELECT DISTINCT CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDate
FROM dbo.ProviderProcessGeneratedDate
ORDER BY CONVERT(Varchar(10), GeneratedDate, 112)
Upvotes: 0
Reputation: 25376
Just to add to answers above...
You can convert back to string outside of tsql. Just return datetime type and convert to a date format you want in code (display layer).
Upvotes: 1
Reputation: 29955
Perhaps you just need to use another field alias so that it uses the date for the ordering rather than the formatted string? I.e.
SELECT DISTINCT CONVERT(Varchar(10), GeneratedDate, 101) AS GeneratedDateString
FROM dbo.ProviderProcessGeneratedDate
ORDER BY GeneratedDate
Although in most cases you should really be leaving the date as a date and doing any required formatting in the UI at the last moment possible.
Upvotes: 0
Reputation: 33944
I think you can use:
ORDER BY dbo.ProviderProcessGeneratedDate.GeneratedDate
to force it to use the value from the original table, instead of your new modified value? You could even add an alias to the FROM clause:
FROM dbo.ProviderProcessGeneratedDate ppgd
So that you could use the "ppgd" alias instead of the whole tablename in my first statement.
Upvotes: 0