SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008                  

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

You misunderstand how date values work in a database. There is no human-readable format. When you see DateTime or DateTime2 values formatted as yyyy-mm-dd what you're seeing is something shown by your debugger or query tool for convenience; the actual value used in the database is binary, not human readable, and is intended to be efficient for storage, indexing, and date arithmetic.

If you need to see a value in a specific format, you must convert() or format() it to a type in the varchar family as part of the query. Or, even better, let your application code/reporting tool do this for you, and just return the original value.

I also see indication these dates are potentially stored originally in a varchar, or nvarchar column. If so, it is a major flaw in the schema design. You will get significant performance benefits and save yourself some big headaches down the road if you can start storing these values using a type from the DateTime family in the first place.

With this in mind, and because it's not clear what you're starting from, let's look at five scenarios, in order of preference:

The column already uses a type from the DateTime family, and you can let your application/reporting tool handle the format

Good for you using a real DateTime value in the schema. That's what we expect to see. Even better, suddenly everything gets really simple in your SQL and the entire snippet in the question reduces to just this:

 ai.[Due Date] AS [TD]

The column already uses a type from the DateTime family, but the client system can't format

This is still pretty good. The schema is still okay, and in this case we can still simplify the original code somewhat:

COALESCE(
    CASE WHEN ai.[Due Date] < GETDATE() THEN '[due] ' ELSE '' END 
    + FORMAT(ai.[Due Date], 'MM-dd-yyyy')
, ' ') AS [TD]

The column uses the a type from varchar family, but you can fix it to use DateTime2

I say "fix" here, because now the schema really is broken as is. But that's okay: you can fix it. Do that. Then use the code from a previous scenario.

The column uses the a type from varchar family and you can't fix it, but at least the raw data always uses a semantic 'yyyy-MM-dd` format

Bummer. You're stuck with a broken schema. But we can at least take advantage of the well-formatted data to make things much more efficient by using cast/convert on the get_date() expression to match the column, rather than vice versa as it is now, like this:

WHEN ai.[Due Date] < CONVERT(varchar, GETDATE(), 120)

Now we're doing a string comparison instead of a date comparison, which is generally slower and, well, just wrong. But we can get away with it because of the nice format in the data, and the saving grace is we only need to cast the one get_date() value, rather than every single row we have. Moreover, this way any index on the column would still be valid. The code snippet on the question would be unable to use any index on the [Due Date] column. I know this is a SELECT clause, but this is worth remembering for the general case.

The full solution for this scenario now looks like this:

COALESCE(
    CASE WHEN ai.[Due Date] < CONVERT(varchar, GETDATE(), 120) THEN '[due] ' ELSE '' END
    + FORMAT(CAST(ai.[Due Date]) AS Date), 'MM-dd-yyyy')
, ' ') AS [TD]

Again, only do this if you can't get your raw column data into a DateTime format. That is what you really want here.

The column uses the a type from varchar family, you can't fix it, and the format is not semantic or not consistent

Oh boy. This is where you really don't want to be. If you can do nothing else, at least see if you can start getting consistent and semantic values into your column. At this point, we are stuck with doing extra work on every row we have (possibly more than once) for pretty much every query. Here we go:

COALESCE(
    CASE WHEN CAST(ai.[Due Date] AS DATETIME2)  < GETDATE() THEN '[due] ' ELSE '' END 
    + FORMAT(CAST(ai.[Due Date] AS DATETIME2), 'MM-dd-yyyy')
, ' ') AS [TD]

The code doesn't look much different than other options, but the performance characteristics will be extremely different... potentially multiple orders of magnitude worse.

Remember: because of internationalization and time zone issues, converting between strings and dates is surprisingly slow and expensive. Avoid doing that whenever possible in all your queries.