order by 2 columns and nulls last - sql 2000

I want the query to returns the values in a specified order.
I want order by modified date first, then created date....easy enough right?
order by modified_date, created_date

The problem is, that in the modified date is not always filled in, so its null, so when I do the above query, the nulls are listed first in the results..but I want records with value in that field to be shown first..then sorted by the second column of created_date....then the null fields for modified_date are shown after that...

is this possible?
Who is Participating?
metechguruConnect With a Mentor Commented:
use COALESCE(modified_date,"9999-12-12") in order by
solraccheffyAuthor Commented:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name '9999-12-12'.
solraccheffyAuthor Commented:
ok, nevermind that last post, just a quotes issue...

but how do I incorporate time as well, I want correct order based on the time of day as well...

please advise.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

solraccheffyAuthor Commented:
was really hoping to hear back on this....the date part is ordering correctly but the time is not....how do I get it so the time portion of the date is included in the order by
order by CAST('modified_date' AS datetime)
Parth MalhanConnect With a Mentor Principal EngineerCommented:
you can use order by modified_date, created_date desc
solraccheffyAuthor Commented:
Angell your idea sounds fine - sorry I didnt get back to this question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.