Order by date - now DESC

[tjock]

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

 

[SQLSister]

What data type is your date field?

Questions about posting. See faq183-874

 

[tjock]

the date date field is nvarchar. Should it be something else, and if so what would be my order by clause?

 

[mrees]

Hi,

How is your char displayed? dd/mm/yyyy, mm/dd/yy etc?

You could try

ORDER BY
convert(smalldatetime,YOURDATE,103)

Or choose a style that closely fits your format.

mrees

 

[SQLSister]

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.

Questions about posting. See faq183-874

 

[tjock]

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.

 

[mrees]

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.

mrees

 

[SQLSister]

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?


Questions about posting. See faq183-874

 

[ESquared]

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.)

 

[tjock]

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|
| |
| |
| |
| |
| |

Thanks

 

[SQLSister]

Then Esquared's solution can be modified

ORDER BY
   CASE WHEN DateField is null THEN 1 ELSE 0 END ASC,
   DateField ASC

Questions about posting. See faq183-874