Order by descending date - month, day, and year

Understanding how SQL sorts dates

When working with date sorting in SQL, right off the bat, you need to ensure that the column storing the dates is either of date or datetime data type. This will make your life easier plus, you’ll witness smooth sailing when it comes to date ordering by the database engine.

If by any chance the dates are stored as varchar or any other non-date data types, you may have to flex the muscle of the CONVERT function that changes them into date values. This will promote proper sorting:

-- Our "magic wand" for turning non-date values to dates
SELECT *
FROM your_table
WHERE ISDATE(your_date_column) = 1
ORDER BY CONVERT(datetime, your_date_column) DESC;


The hero above is the ISDATE() function. It helps to exclude non-date values that could cause a hit-and-run accident in your scripts by either causing errors or getting sorted incorrectly. They are sorted at the very end of our result.

Tackling inconsistent date entries

Here’s a curveball, you might run into date entries resembling a roller coaster ride: some poorly formatted, others inconsistent. A CASE statement is enough to net these.

-- Because bad dates also deserve a place, just not at the top
SELECT *
FROM your_table
ORDER BY 
  CASE 
    WHEN ISDATE(your_date_column) = 1 THEN CONVERT(datetime, your_date_column)
    ELSE NULL
  END DESC;


What if I told you that valid dates are sorted first and non-date or invalid dates are sent to the bottom of the results? Well, this has just happened!

Evaluating your query performance

Using functions such as CONVERT and ISDATE may feel heavy on the performance side. Pay attention to this especially when wrangling large datasets that require a complete table scan. It’s like asking a library intern to count all the books in the library on his first day at work – get where I am heading, right?

Storing dates in their appropriate types can save you this overhead — and trust me, your database will thank you for this later.

Advanced sorting techniques

For the seasoned SQL warriors who love to battle more complex scenarios, you will occasionally need to sort by year, month, and day independently. If your_date_column is of non-date type, here is how you can convert it and then sort the dates:

-- When ascending is too mainstream
SELECT *
FROM your_table
ORDER BY 
  YEAR(CONVERT(datetime, your_date_column)) DESC,
  MONTH(CONVERT(datetime, your_date_column)) DESC,
  DAY(CONVERT(datetime, your_date_column)) DESC;


However, if your_date_column is already a date or datetime data type, simply sorting by the column alone will inherently consider the year, month, and day components in the sorting.

Building consistent tables

Future-proofing your database and ensuring reliability involves having dates stored consistently across the database. Store dates as date data type, which guarantees date validation and efficient storage.

Keep in mind that making changes at the schema level to convert legacy date storage formats into the date type will save time and computational resources in the long run. Always back your changes with relevant tests to ensure your queries run smooth as silk.

##Handling different date formats

When serving a global audience or dealing with legacy databases, you have to make peace with different date formats. This could be DD/MM/YYYY vs MM/DD/YYYY. Aim for storing date components separately or use industry-standard formats like ISO 8601.

While handling varying formats, a robust CASE structure in your ORDER BY clause is vital, or better yet, create appropriate indexing on date components for smooth ordering.