Sorting by date SQL server

Question

2.33/5 (2 votes)

See more:

I have a table with events with a column called Event Date.
I want to order it so that the Latest event date (closes to the current system date) is on top with the rest following underneath and then as soon as that date passed it should move to the bottom with the rest.

I don't know if there is a way to do what I want but I have searched the internet and I found a few things that might be what I am looking for but its not exactly working..
Here are some examples

SQL

SELECT * FROM events
ORDER BY EVENTDATE ASC,
CASE EVENTDATE WHEN 'future' THEN EVENTDATE END ASC,
CASE WHEN EVENTDATE <> 'future' THEN EVENTDATE END DESC

This one gave me an error : Conversion failed when converting date and/or time from character string.

Then I tried this:

SQL

SELECT * 
FROM mtc3_doradosbc.cw_events  
ORDER BY
CASE WHEN EVENTDATE > GETDATE().now THEN 1
     WHEN EVENTDATE < GETDATE() THEN 2
END ASC,
EVENTDATE

and this

SQL

SELECT *, EVENTDATE(date, GETDATE()) FROM mtc3_doradosbc.cw_events
order by CASE WHEN mtc3_doradosbc.cw_events < 0 THEN 1 ELSE 0 END, diff

If any one could assist i would appreciate it greatly
Thank you

Example ( this is what i have)
Event 1 ----10 Mar 2014
Event 2 ----28 mar 2014
Event 25----25 Mar 2014

I want on the 25 Mar 2014
Event 25----25 Mar 2014
Event 2 ----28 mar 2014
Event 1 ----10 Mar 2014

I want on the 26 Mar 2014
Event 2 ----28 mar 2014
Event 25----25 Mar 2014
Event 1 ----10 Mar 2014

Posted 25-Mar-14 2:25am

Member 10395722

Updated 25-Mar-14 2:59am

v2

Add a Solution

Comments

Maciej Los 25-Mar-14 8:55am

Improve your question and post sample data. It would be easier to understand your issue.

Member 10395722 25-Mar-14 8:56am

What is it that you need me to improve on ? i think the question is clear enough, there is nothing more i can say

Maciej Los 25-Mar-14 9:01am

Thank you for improving question ;)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jawad Shairf · Answer 1 · 2014-03-25T03:01:00

Solution 1

there was a possible miskate in the second block of code.
you can try this

SELECT *
FROM mtc3_doradosbc.cw_events
ORDER BY
CASE WHEN EVENTDATE >= GETDATE() THEN 1
WHEN EVENTDATE < GETDATE() THEN 2
END ASC,
EVENTDATE ASC

Posted 25-Mar-14 3:01am

Jawad Shairf

Comments

Member 10395722 25-Mar-14 9:31am

I used this, sort of do what i want although it shows the next day's event at the top instead of todays one and then the next day

Maciej Los · Answer 2 · 2014-03-25T03:04:00

Solution 2

I think you're looking for DATEDIFF function[^].

SQL

SELECT Event, EventDate
FROM EventTable
ORDER BY DATEDIFF(d, EventDate, GETDATE()) ASC

Posted 25-Mar-14 3:04am

Maciej Los

Comments

Member 10395722 25-Mar-14 9:28am

This does not work, it still shows tomorrow's event first