Why better not format in the Query?

Let's start first with the reason you better should not format in the Query.

Formatting Data is necessary to present that Data in a way that people are used to. A Date for example is stored like "yyyy-MM-dd hh:mm:sss" but the user wants to see "dd-MM-yyyy". But a Date is a different Data Type for a reason. And one reason for that is sorting data.

Take a look at what happens if you sort the formatted date:

with DatesTable
as
(
  select cast('20220101' as datetime) as TheDate
  union all
  select dateadd(Day, 1, TheDate)
  from DatesTable
  where dateadd(Day, 1, TheDate) <= '20220630'
)
select format(TheDate, 'dd-MM-yyyy')
from DatesTable
order by format(TheDate, 'dd-MM-yyyy')
option (maxrecursion 0)

As you can see the Data is sorted alphabetically, not chronologically.

Rounding Numbers or converting them to strings is also not such a good idea. Let me show you that with the following Query.

declare @A float = 5.003
declare @B float = 5.003
declare @C nvarchar(10)
declare @D nvarchar(10)

select @A + @B
select round(@A + @B, 2)

set @A = round(@A, 2)
set @B = round(@B, 2)

select @A + @B

set @C = format(@A, '0.00')
set @D = format(@B, '0.00')

select @C + @D

As you can see, formatting Data in the Query already might give you unexpected results later on. You might end up with sorting problems, rounding errors and some calculations could give errors.

Why you would format Data

So, if the Data is used in an application or reporting software, formatting the Data in the Query is not such a good idea. If for some reason it is necessary, I usualy include the original value in its original Data Type and the formatted value. Like OrderDate and OrderDateFormatted.

Formatted Values might by necessary when working with Business Process Management Tools (like Orbis or BPM Software). Typically such Tools do things in the background, like creating documents, send out emails, moving data from one system to another and such. Now of course such Tools have formatting functions as well, certainly those I mentioned and worked with. But especially when you need to put together a lot of text, I find it easier to work with the Data already formatted in advance.

So here are some tips on formatting Data in case you need it.

Formatting Dates

By far the easiest way to format a Date is to use the Format Function:

select format(getdate(), 'dd-MM-yyyy HH:mm')

As you can see you need to use capital M for the month and lower case m for the minutes. Hours can be done as HH which will give you the 24 hour format, or as mm which will give you the 12 hour format.

Instead of dd you could also use ddd which will give you the abbreviated version of the name of the day. Or you could use dddd which would give you just the name of the day. Similarly you could use MMM to get the abbreviation of the month, or MMMM for the full name of the month.

select format(getdate(), 'ddd dd MMM yyyy HH:mm')
select format(getdate(), 'dddd dd MMMM yyyy HH:mm')

Now you might think it is a shame it is not in your language. But wait, there is an optional Parameter you can use with the Format Function. You can specify which Culture you want to use. So if you want the result to be in Dutch, you could use this one:

select format(getdate(), 'dddd dd MMMM yyyy HH:mm', 'nl-NL')

You can find a list of all the available Cultures by clicking here.

Formating Numbers

The Format Function will also be your best friend when you want to format Numbers. When you have Floats, you can use 'N' as the second Parameter of the Format Function. Take a look at these examples:

declare @Number float = 12345.678

-- Format as a Number, default 2 decimals
select format(@Number, 'N')
-- Use no decimals
select format(@Number, 'N0')
-- Use 1 decimal
select format(@Number, 'N1')
-- Use 3 decimals
select format(@Number, 'N3')
-- Use Dutch Format
select format(@Number, 'N', 'nl-NL')

Instead of 'N' you could also use 'C' which works similar but also adds a currency symbol corresponding with the chosen culture:

declare @Number float = 12345.678

-- Use Dutch Format
select format(@Number, 'C', 'nl-NL')

When working with Integers you could use 'D' as the second Parameter which stands for the number of Digits. This might come in handy if you want the number to have a fixed length:

-- Add leading zeros
select format(1, 'D6')
select format(12, 'D6')
select format(123, 'D6')
-- but you cannot make it shorter
select format(1, 'D2')
select format(12, 'D2')
select format(123, 'D2')

You could also use a free format as the second Parameter. This is one I use often when working with Exact Synergy where the Request and Document Number are presented in a specific format: 00.000.000. Here is what I use for that:

-- What you might think
select format(12345, '00.000.000')
-- What it should be
select format(12345, '00\.000\.000')

As you can see using just the dots will not work because SQL Server confuses it with Decimal Points. But by using a Backslash in front of a dot to escape the character, SQL Server understands it should be taken literal.

That's it for today!

Back in the day when I started with SQL Server the Format Function was not available in SQL Server 2000. So to format a Date as dd-MM-yyyy I used this one:

select
right('00' + cast(datepart(Day, getdate()) as nvarchar(2)), 2) +
'-' +
right('00' + cast(datepart(Month, getdate()) as nvarchar(2)), 2) +
'-' +
cast(datepart(Year, getdate()) as nvarchar(4))

As you can see it is a lot of work. Getting just the Day from the Date. Since that can be less than 10, you need to put 2 zeros in front of it and then take the 2 characters on the right. The same goes for the Month. Luckily the year will always be 4 Digits.

Also, welcome to Bracket Hell where a missing or misplaced Bracket will take you some time to figure out which one.

You can imagine my surprise when in 2017 I saw a co-worker got the same result with just one line of code:

select format(getdate(), 'dd-MM-yyyy')

My excuse? Well my version is backwards compatible while his would fail on older SQL Server versions. Which was a bit of a lame, made up excuse, since it was 2017 and the Format Function was available since SQL Server 2012.

Then again, last year (2022) I had to fall back on my old verbose version with a customer who was still using SQL Server 2008.

Moral of this story: Even though (or even more so when) you have the skills, don't forget to read the SQL Server release notes when a new version comes out. And update your skills continuously.

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!