Sort by Date in SQL
ORDER BY
(MONTH(resource_date) - MONTH(GETDATE()) + 12) % 12,
DATEADD(year, YEAR(GETDATE()) - YEAR(resource_date), resource_date),
YEAR(resource_date)
The first term sets the primary order by the month of resource_date
(the current month will be first, the previous one, last). The second term orders the timestamps within a month regardless of the year of the date. If your dates do not contain time parts or if the time parts are absolutely irrelevant, you could replace it with DAY(resource_date)
. Finally, the last term takes the year into account for otherwise identical dates (could also be simply resource_date
).
Related videos on Youtube
Brett
Updated on June 04, 2022Comments
-
Brett over 1 year
I have a resources table, one of the fields is a date field with the Data Type of date. I want to have to following output:
Current month records (say May - year is not important)
Then the following (again, assuming May is the current month)
- June Records
- July Records
- August Records
- September Records
- October Records
- November Records
- December Records
- January Records
- February Records
- March Records
- April Records
Come June, June is the current month and then the order would be:
- July Records
- August Records
- ...
Here is my SQL...I don't know how to ORDER the output to achieve the desired order (5,6,7,8,9,10,11,12,1,2,3,4):
SELECT resource_id, resource_title, resource_summary, resource_category, resource_status, resource_date, DATEPART(month, resource_date) AS resource_month, DATEPART(day, resource_date) AS resource_day FROM dbo.resources WHERE (resource_category = N'Quotes') AND (resource_status <> N'Draft')
I found this possible solution for MySQL:
but I'm missing something on my end.
-
Brett over 12 yearsOkay, this is the first suggestion I tried. In MS SQL, it is listing the records correctly. When I output the results to a web page, the order is not correct. Here is a sample: americanspeaker.com/beta/members/sample.asp
-
Brett over 12 yearsHere is the SQL in MS SQL SELECT TOP (100) PERCENT resource_id, resource_title, resource_summary, resource_source, resource_date, resource_category, resource_status FROM dbo.resources WHERE (resource_category = N'Quotes') AND (NOT (resource_status = N'Draft')) ORDER BY (MONTH(resource_date) - MONTH(GETDATE()) + 12) % 12, DAY(resource_date)
-
Brett over 12 yearsHere is the code that pulls from the DB Dim rs_quotes Dim rs_quotes_cmd Dim rs_quotes_numRows Set rs_quotes_cmd = Server.CreateObject ("ADODB.Command") rs_quotes_cmd.ActiveConnection = MM_americanspeaker_STRING rs_quotes_cmd.CommandText = "SELECT * FROM dbo.qry_resources_quotes" rs_quotes_cmd.Prepared = true Set rs_quotes = rs_quotes_cmd.Execute rs_quotes_numRows = 0
-
Andriy M over 12 yearsThe ORDER BY should be applied to the final query, if you want guaranteed order. You must not rely on the ORDER BY clause in the definition of
dbo.qry_resources_quotes
(which, I assume, is defined by the previous query, theSELECT TOP (100) PERCENT...
one). -
Brett over 12 yearsWorks! Awesome. I've run into that ORDER BY issue in the past. Is this a bug in MS SQL or something I'm not understanding about SQL and MS SQL Server?
-
Andriy M over 12 yearsFrom the MSDN article about
CREATE VIEW
:Note. The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.