CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-order-by
Brett
Brett

Reputation: 887

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)

Come June, June is the current month and then the order would be:

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.

Upvotes: 4

Views: 17116

Answers (6)

Manfred Sorg
Manfred Sorg

Reputation: 1890

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

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

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

Upvotes: 4

mu is too short
mu is too short

Reputation: 434585

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)

Upvotes: 0

Mike Shepard
Mike Shepard

Reputation: 18146

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.

Upvotes: 0

King Skippus
King Skippus

Reputation: 3826

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;

Upvotes: 0

a1ex07
a1ex07

Reputation: 37354

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

Upvotes: 0

Related Questions