Sort by Date in SQL

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 th开发者_如何学运维e 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:

I need unusual ordering mysql results

but I'm missing something on my end.

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).

Will it work for you?
ORDER BY
CASE DATEPART(month, resource_date)
WHEN 5 THEN 0
WHEN 6 THEN 1
... etc
END

I think something like this might be what you're looking for:

SELECT
  resource_id,
  resource_title,
  resource_summary,
  resource_category,
  resource_status,
  resource_date
FROM
  dbo.resources
WHERE
  resource_date >= DATE_FORMAT(NOW() ,'%Y-%m-01') AND
  resource_date < DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 YEAR) ,'%Y-%m-01')
ORDER BY
  resource_date;

How 'bout ORDER BY (DATEPART(month,resource_date) - (DATEPART(month,getdate() -1)) % 12)

So in May (month 5), you order by the month in the row -6 (mod 12). So, June (month 6) would be 0, July (7) would be 1.

In June, July would be 0, etc.

You should be able to adapt the MySQL solution by using DATEPART in place of DATE_FORMAT:

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')
ORDER BY DATEPART(month, resource_date) < DATEPART(month, GETDATE()),
         DATEPART(month, resource_date)

I don't have SQL Server handy so I'm not sure if it will be happy with a boolean in the ORDER BY clause though. If it doesn't like the boolean ORDER BY, then a CASE should do the trick:

ORDER BY
    CASE WHEN DATEPART(month, resource_date) < DATEPART(month, GETDATE())
        THEN 0
        ELSE 1
    END,
    DATEPART(month, resource_date)

I assume that there is a year within "resource_date" - isn't it? In this case you can simply filter and order by

WHERE resource_date >= getdate()
  AND resource_date < DATEADD(year,1,getdate())
ORDER BY resource_date;

If there is no year (or more exactly: different unknown years) you can do this:

ORDER BY
    CASE
    WHEN DATEADD(year,-year(resource_date),resource_date) <
         DATEADD(year,-year(getdate()),getdate())
    THEN 1
    ELSE 0
    END ASC,
    DATEADD(year,-year(resource_date),resource_date);

Hope it helped ...