T-SQL: Formatting Numbers and Dates with FORMAT vs. CONVERT

Over the (relatively) long history of T-SQL and SQL Server, when we (as programmers) needed to format a date or a number inside our SQL we primarily had access to two commands:

CONVERT()
CAST()

Of these, CAST() is intended primarily for converting data field from one type to another, but CONVERT() also has the capability to format the data built in. You access this functionality by CONVERT-ing a date or a number to a VARCHAR type, and supplying a style.

Reading through the article referenced above is very useful. If you have not done it already, give it a go. Consider the section on Implicit Conversions carefully. For very Microsoft-like reasons, T-SQL will often do an implicit conversion behind the scenes when you do something like:

SELECT VarcharField + ‘-‘ + NVarCharField ;

You really need to keep an eye out for this scenario – when SQL runs across this conversion (making both elements the same type) it does not throw an error immediately, instead it attempts to correct the issue dynamically. If that fails, however, then you will get an error.

Letting SQL attempt the implied conversion has a big performance impact. But if you explicitly CAST the VARCHAR to NVARCHAR (or vice versa) you do not get that drag on performance. Why? As far as I can tell, an explicit conversion of NVARCHAR <> VARCHAR allows the execution plan generation to load the translation table one time, then it uses that structure over and over. If there is an implicit conversion, however, the translation table is loaded per row – which might be thousands or millions of times – and that makes things very slow indeed.

But let us get back to the question at hand! If we need to make a data element a specific format – usually it is a date, in a format a vendor needs, and one that is not in the standard list of styles.

An example of this would be:

Month/Day/Year Hour:Minute:Second (or MM/DD/YYYY HH:MM:SS)

…where the hour is in 24-hour time. There is no default MS-supplied style for that!

With just CONVERT() in the toolbox (CAST cannot help us, it does not have styles) we have to do two operations and combine them:

Note that we set a length in the VARCHAR, which will truncate any extra characters in the converted string beyond that length. This is useful when we need the first section of a converted string, but not all of it.

We might also CONVERT something to a style, and then do REPLACE() or STUFF() to manipulate it further… but we also have another, more direct option:

FORMAT()

This function, in some circumstances, will let us apply essentially any output format we want without needing to combine multiple operations. The output format keywords are available for Numbers and Dates / Times.

It requires, however, that your SQL query user / process have access to the CLR (.NET Common Language Runtime) on the server you are running the query on. If you do not have access to the CLR …sadly you are out of luck.

But test to see if it works!

Note that the format string is an NVARCHAR type, so you want to prefix your string with N’ to prevent an implied CAST…

If this does not run, then no CLR. Do not worry, we will be here when you come back from getting a stiff drink.

If this does work, then you can create, in one step, any kind of date, time and numeric output you need.

Extra Credit

If you can’t use FORMAT to zero-fill a number that you’re converting to a VARCHAR, you can do it with CONVERT(), REPLICATE() and RIGHT(). Your job? Figure out how!

And…

As ever, if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!