I need resulkts to Order by date, now() DESC. Just like if you sorted by date in a windows explorer GUI. Simply Order by date doesn't do this - anyone know how?
Thanks
The sort order for an nvarchar field is quite different than the sort order for a datetime field. It is best to always store dates ina a datetime field. If you cannot change the structure, then you need to convert the date field to a datetime within the order by using cast or convert. Be aware though that this is not very efficient and may significantly slow down the query if you have lots of records.
I was able to convert the field to datetime, but the order by duedate brings future dates to the top - is there a way to alter the clause so that it brings dates closest to now() to the top? I now this may be tricky this there are past and furture date records.
Where do you want the records that are in the past to go? If you remove the DESC from your ORDER BY, the earliest will be the first record, the one most in the future the last.
OK let's ask how you want to handle this? Do you want to show future dates? CAn you just exclude them from the query?
Or if you want both do you want the actual dates first, then the future dates or the dates in whatever distance they are from the current date?
What I mean is that if you have dates of
12/1/2004
11/16/2004
11/19/2004
11/18/2004
and today's date is 11/18/2004, then what order would you want them to be in? Aslo is time important?
ORDER BY
CASE WHEN DateField > GetDate() THEN 1 ELSE 0 END ASC,
DateField DESC
-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)
Thanks. I do want to show future dates, but time is not important. So I use the standard order by date to get the output shown below, but I have empty/null value fields, so it looks like this
FIELD NAME
| |
| |
| |
| |
| |
|11/16/2004|
|11/18/2004|
|11/19/2004|
|12/01/2004|
But I'd like the SQLouput to be this:
FIELD NAME
|11/16/2004|
|11/18/2004|
|11/19/2004|
|12/01/2004|
| |
| |
| |
| |
| |
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.