Can you order by on datename?
-
Hi guys,
SQL doesnt appear to let you use order by on datename.. eg:
SELECT distinct datename(mm, events.date) as Months
FROM Events
where datepart(month, events.date) between 5 and 6
order by datename(mm, events.date)
is this a known problem?
-
Have found a work around.. no worries!
-
datename(mm,events.date) will give you 'May' and 'June' from your where clause. Ordering by the NAME will put June first.
Is this what you want? Maybe order by datepart, not datename.
-
Fred,
im such a dumbass... of course it's ordering- it's ordering alphabetically! i thought for soem reason it would order still based on JFMAMJASOND order...
der..!
-
Joe's answer is close to correct, but it has a small error in it. He should be using the DATENAME function instead of DATEPART. Also, the DATENAME function returns the entire month name, not the first three characters of the month name. Therefore, the correct solution for the column sort_value is:
select charindex(left(datename(mm,getdate()),3),'JanFebMarAprMayJunJulAugSepOctNovDec') AS sort_value
-
Actually the correct solution to Alex's original question is:
SELECT distinct datename(mm, events.date) as Months
FROM Events
where datepart(month, events.date) between 5 and 6
order by charindex(left(datename(mm,events.date),3),'JanFebMarAprMayJunJulAugSepOctNovDec')
-
That set of functions starting with CHARINDEX is clever! I've already cut & pasted it into my box of tricks.
-
Just to add even more fuel to this post, it would be better to use the bewteen operator for this task as it would use an index seek and not a scan...
SELECT distinct datename(mm, events.date) as Months
FROM Events
where datepart(month, events.date) between @DateStart and @DateEnd
order by charindex(left(datename(mm,events.date),3),'JanFebMarAprMayJunJulAugSepOctNovDec')
this is assuming that he wants only the events of May and June for the current year.
And wouldn't it be simpler to just do a sort like this ??
Order by datepart(month, events.date)
-
Or even...
Order by events.date????????
Because without a second column to sort on, the previous order by doesn't mean a lot...
-
--I got an error when I tried. So
/*
Server: Msg 145, Level 15, State 1, Line 47
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
*/
SET NOCOUNT ON
DECLARE @events TABLE
(
[Date] DateTime
)
INSERT @events VALUES ('01/01/2003')
INSERT @events VALUES ('02/01/2003')
INSERT @events VALUES ('03/01/2003')
INSERT @events VALUES ('04/01/2003')
INSERT @events VALUES ('05/01/2003')
INSERT @events VALUES ('06/01/2003')
INSERT @events VALUES ('07/01/2003')
INSERT @events VALUES ('08/01/2003')
INSERT @events VALUES ('09/01/2003')
INSERT @events VALUES ('10/01/2003')
INSERT @events VALUES ('11/01/2003')
INSERT @events VALUES ('12/01/2003')
INSERT @events VALUES ('01/01/2004')
INSERT @events VALUES ('02/01/2004')
INSERT @events VALUES ('03/01/2004')
INSERT @events VALUES ('04/01/2004')
INSERT @events VALUES ('05/01/2004')
INSERT @events VALUES ('06/01/2004')
INSERT @events VALUES ('07/01/2004')
INSERT @events VALUES ('08/01/2004')
INSERT @events VALUES ('09/01/2004')
INSERT @events VALUES ('10/01/2004')
INSERT @events VALUES ('11/01/2004')
INSERT @events VALUES ('12/01/2004')
SELECT DISTINCT datepart(month, A.date) ABR, datename(mm, A.date) as Months
FROM @events A
where datepart(month, A.date) between 1 and 12
ORDER BY datepart(month, A.date)
OR Your's
SELECT DISTINCT charindex(left(datename(mm,A.date),3),'JanFebMarAprMayJunJulAugSepOctNovDec') ABR, datename(mm, A.date) as Months
FROM @Events A
where datepart(month, A.date) between 1 and 12
order by charindex(left(datename(mm,A.date),3),'JanFebMarAprMayJunJulAugSepOctNovDec')
Regards,
gova -
Order by 1 will not be supported in 2005... better get used to put the real column in there.
-
govinn:
Good catch. I didn't unit test the solution first. Without writing a subquery, you need to include the item you want to sort by in the SELECT statement also. Therefore, the two queries you wrote above do execute correctly, except they also output the column's value: either datepart(month,A.date) or
charindex(left(datename(mm,A.date),3),'JanFebMarAprMayJunJulAugSepOctNovDec').
--Jeff
-
i'm sorry - did i miss something?
wouldn't you sort by month like this...
examples using Northwind :
select * from [orders]
order by Month(orderdate)
select * from orders
order by Datepart(mm, orderdate)
-
he wants to sort by Jan, feb, march, apr...
not August, december....
-
exactly...
that's by month alone. here's by month / day - if you need to sort by day as well.
select * from orders
order by Datepart(mm, orderdate), Datepart(dd, orderdate)
http://www.7hertz.com/archives/2004/04/its_your_birthday.html
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply