Author |
Topic |
Dan1980
Starting Member
9 Posts |
Posted - 2013-10-30 : 11:17:58
|
Hello allHoping 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 workingorder by SSD_SED >= GETDATE()Where the date field is SSD_SEDProably easy but I'm brand new to SQLRegards |
|
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 |
|
|
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 AMWHERE ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 0)Be One with the OptimizerTG |
|
|
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 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-30 : 11:59:49
|
as I said you are confusing WHERE and ORDER BYSELECT...FROM ...WHERE ssd_sec >= dateadd(day, datediff(day, 0, getdate()), 0)ORDER BY SSD_SEDEDIT: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 OptimizerTG |
|
|
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] |
|
|
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 ENDMadhivananFailing to plan is Planning to fail |
|
|
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. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-10-31 : 06:50:49
|
TryORDER 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 MadhivananFailing to plan is Planning to fail |
|
|
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] |
|
|
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 |
|
|
|