SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sorting by today's date and greater
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dan1980
Starting Member

United Kingdom
9 Posts

Posted - 10/30/2013 :  11:17:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 10/30/2013 :  11:21:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 10/30/2013 :  11:22:16  Show Profile  Reply with Quote
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

Edited by - TG on 10/30/2013 11:23:10
Go to Top of Page

Dan1980
Starting Member

United Kingdom
9 Posts

Posted - 10/30/2013 :  11:31:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 10/30/2013 :  11:59:49  Show Profile  Reply with Quote
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

Edited by - TG on 10/30/2013 12:02:34
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 10/30/2013 :  22:57:59  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/31/2013 :  02:40:17  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

United Kingdom
9 Posts

Posted - 10/31/2013 :  04:42:45  Show Profile  Reply with Quote
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

India
22744 Posts

Posted - 10/31/2013 :  06:50:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 10/31/2013 06:54:03
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 10/31/2013 :  10:01:58  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

Dan1980
Starting Member

United Kingdom
9 Posts

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

Thanks for your help
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000