sorting by today's date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sorting by today's date

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-10-29 : 02:46:59

i have a query which gets me result and currently sorted by date in DESC order
i want to order it so today's date will always be on top
and after that all the other date sorted in DESC order
how can i do it?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2006-10-29 : 03:07:52

[code]
ORDER BY CASE WHEN MyColumn >= DATEADD(Day, DATEDIFF(Day, MyColumn, GetDate()), 0)
THEN 1
ELSE 2
END,
MyColumn
[/code]
Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-10-29 : 03:39:07

iput it in the order of the syntax and it didnt work (it gave me the most far date from the history as first)
so i putted it as part of the select and order it by that
thnaks for the idea
and if u can tell me where i did wromg with the above query as part of the order by it will be great
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2006-10-29 : 07:29:25

"if u can tell me where i did wromg with the above query"

Well, you'll need to post the query for us to be able to work out what you did wrong!

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-29 : 08:03:30

ORDER BY ABS(SIGN(DATEDIFF(day, MyDateColumn, GETDATE()))), MyDateColumn DESC

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-10-29 : 10:17:08

thnaks alot PESO
your answer soolvs my problem almost 100%!
how can i make this order :
firt : TODAYS DATE
second : ALL the future date up to today's date in DESC ORDER
third : ALL the PAST date's up to today's date in DESC ORDER
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-29 : 10:21:48

quote:
Originally posted by pelegk2

thnaks alot PESO
your answer soolvs my problem almost 100%!
how can i make this order :
firt : TODAYS DATE
second : ALL the future date up to today's date in DESC ORDER
third : ALL the PAST date's up to today's date in DESC ORDER
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

use Kristen's method. It is easier to add on the additional sorting criteria

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-29 : 10:52:37

[code]ORDER BY CASE
WHEN DATEDIFF(day, MyDateColumn, GETDATE()))) = 0 THEN 0
WHEN DATEDIFF(day, MyDateColumn, GETDATE()))) < 0 THEN 1
ELSE 2
END,
MyDateColumn DESC[/code]

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-10-29 : 11:28:07

"use Kristen's method"

Yeah, but mine doesn't work:

"put it in the order of the syntax and it didnt work"

Of course if we could see the query, as I asked, we could give some accurate advice without wasting our time guessing what is going on ...

... and Peso your version of my method is a bit "bracket-heavy"

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-29 : 12:00:12

Yes, at least 2 brackets too much, it looks like...

ORDER BY	CASE
			WHEN DATEDIFF(day, MyDateColumn, GETDATE()))) = 0 THEN 0
			WHEN DATEDIFF(day, MyDateColumn, GETDATE()))) < 0 THEN 1
			ELSE 2
		END,
		MyDateColumn DESC

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-29 : 17:15:40

quote:
Originally posted by Kristen

"use Kristen's method"

Yeah, but mine doesn't work:

"put it in the order of the syntax and it didnt work"

Actually it works. With slight modification. Well the idea works

ORDER BY CASE WHEN MyColumn >= DATEADD(Day, DATEDIFF(Day, MyColumn 0, GetDate()), 0)
              THEN 1
              ELSE 2
              END,
         MyColumn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources