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
 General SQL Server Forums
 New to SQL Server Programming
 Sorting by today's date and greater

Author  Topic 

Dan1980
Starting Member

9 Posts

Posted - 2013-10-30 : 11:17:58
Hello all

Hoping somenone can help. I want SQL to look at a date field and sort the data by todays date and greater. Even though there may be some data older than today.

I've tried something like this but not working

order by SSD_SED >= GETDATE()

Where the date field is SSD_SED

Proably easy but I'm brand new to SQL

Regards

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-30 : 11:21:46
Wouldn't just sorting by SSD_SED in descending order accomplish that? Or are you looking for something more?
ORDER BY SSD_SED  DESC
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-30 : 11:22:16
sounds like you are confusing filtering the data with sorting the data?

If you want to limit your output to only dates that are today and later than do that in a WHERE clause.
If you want to sort the output by date then just say ORDER BY SSD_SED [asc | desc]

Now because GETDATE() returns a datetime for <now> you probably want something like this which returns today at 12:00 AM
WHERE ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 0)

Be One with the Optimizer
TG
Go to Top of Page

Dan1980
Starting Member

9 Posts

Posted - 2013-10-30 : 11:31:32
James K,

Yes I'm looking for today's date first, then tomorrow and so on. In that order.

TG, Thanks.

Tried this but fails

order by SSD_SED WHERE ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 0)

Regards guys

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-30 : 11:59:49
as I said you are confusing WHERE and ORDER BY

SELECT...
FROM ...
WHERE ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 0)
ORDER BY SSD_SED

EDIT:
if you only want results where the date is later than today then include the WHERE clause.

If you want all the results including old dates but ordered by date then exclude the WHERE clause.

Be One with the Optimizer
TG
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-30 : 22:57:59
quote:
Originally posted by Dan1980

James K,

Yes I'm looking for today's date first, then tomorrow and so on. In that order.

TG, Thanks.

Tried this but fails

order by SSD_SED WHERE ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 0)

Regards guys



this is what you want ?

ORDER BY
CASE WHEN ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 0)
AND ssd_sec < dateadd(day, datediff(day, 0, getdate()), 1)
THEN 1 -- Today
WHEN ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 1)
THEN 2 -- tomorrow & onwards
WHEN ssd_sec < dateadd(day, datediff(day, 0, getdate()), 0)
THEN 3 -- yesterday & earlier
END



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-31 : 02:40:17
ORDER BY CASE WHEN ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 0) THEN 1 ELSE 2 END


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Dan1980
Starting Member

9 Posts

Posted - 2013-10-31 : 04:42:45
Thanks guys for all your help.

khtan seems to have put together what I need however it doesn't seem to work. It just fails.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-31 : 06:50:49
Try
ORDER BY 
CASE WHEN ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 0)
AND ssd_sec < dateadd(day, datediff(day, 0, getdate()), 1)
THEN 1 -- Today
WHEN ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 1)
THEN 2 -- tomorrow & onwards
WHEN ssd_sec < dateadd(day, datediff(day, 0, getdate()), 0)
THEN 3 -- yesterday & earlier
END,ssd_sec

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-31 : 10:01:58
quote:
Originally posted by Dan1980

Thanks guys for all your help.

khtan seems to have put together what I need however it doesn't seem to work. It just fails.


what is the error message ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Dan1980
Starting Member

9 Posts

Posted - 2013-10-31 : 11:41:23
I managed to fix with THEN 3 ELSE 4 at the end.

Thanks for your help
Go to Top of Page
   

- Advertisement -