Background

Yesterday, I wanted to provide a bit of documentation to my management.

The data was accurate, but the numbers delivered by SQL Server was missing commas to separate the thousands.

Transact SQL

Format Function

Transact-SQL’s Format Function can help bring that gap.

Background

The Format Function was added in Microsoft SQL Server v2012.

Outline

1. Create a table variable
2. Populate the table variable with a couple of records
3. Display the imported data
   • Query 1
     • Display data as is
   • Query 2
     • Display Data using the system’s default culture
   • Query 3
     • Display data using each record’s culture ( based on the culture column )
   • Query 4
     • Display date of birth using format specifiers

SQL

set nocount on
go

declare @tblPerson table
(
      [id]                  int not null
                            identity(1,1)

    , [name]                varchar(200)        not null

    , [dob]                 datetime            null

    , [salary]              decimal(15,2)       null

    , [balance]             decimal(15,2)       null

    , [lifetimeEarnings]    decimal(20, 2)      null

    , [numberofShares]      decimal(20,2)       null

    , [culture]             varchar(30)         not null

)

insert into @tblPerson
(
    [name], [dob], [salary], [balance], [lifetimeEarnings], [numberofShares], [culture]
)
values
  ( 'Paul McCartney', '06-18-1942', 51000, 1000.00, 1200000, 1910001.75, 'en-gb')
, ( 'Lionel Richie', '06-20-1949', 51000, 1000.00, 1200000, 2828201.35, 'en-us')

select 
          = 'raw'

        , tblP.[name]
        
        , tblP.[dob]
        
        , tblP.[salary]
        
        , tblP.[balance]

        , [lifetimeEarnings]

        , [numberofShares]

from   @tblPerson tblP


select 
          = 'Culture - Current'

        , tblP.[name]
        
        , [dateofBirth]
            = format
            (
                  tblP.[dob]
                , 'D'
            )
        
        , [salary]
            = format
                (
                      tblP.[salary]
                    , 'c'
                )
        
        , [balance]
            = format
                (
                      tblP.[balance]
                    , 'c'
                )
        

        , [lifetimeEarnings]
            = format
                (
                      tblP.[lifetimeEarnings]
                    , 'C0'
                )

        , [numberofShares]
            = format
                (
                      tblP.[numberofShares]
                    , 'N0'
                )

from   @tblPerson tblP

select 
          = 'Culture - Record Specific'

        , tblP.[name]
        
        , [dateofBirth]
            = format
            (
                tblP.[dob]
                , 'd'
                , tblP.culture
            )
        
        , [salary]
            = format
                (
                      tblP.[salary]
                    , 'c'
                    , tblP.culture
                )

        , [balance]
            = format
                (
                      tblP.[balance]
                    , 'c'
                    , tblP.culture
                )
        
        , [lifetimeEarnings]
            = format
                (
                      tblP.lifetimeEarnings
                    , 'C0'
                    , tblP.culture
                )

        , [numberofShares]
            = format
                (
                      tblP.[numberofShares]
                    , 'N0'
                )

from   @tblPerson tblP


select 
          = 'Date of Birth'

        , tblP.[name]
        
        , [dateofBirth - Short Day]
            = format
            (
                  tblP.[dob]
                , 'd'
            )
        
        , [dateofBirth - Long Day]
            = format
            (
                tblP.[dob]
                , 'D'
            )

        , [dateofBirth - RFC1123Pattern]
            = format
            (
                  tblP.[dob]
                , 'MMM-dd-yyyy'
            )
from   @tblPerson tblP

 

Images

 

Source Code Control

GitHub

Gist

DanielAdeniji/TransactSQLFormatFunction.sql

Link

 

Summary

Transact SQL’s Format Function can quickly bail you out.

Unfortunately, a lot of us old timers don’t use it as it a later added function.

It is likely not ANSI SQL.

And, one can do the same from the front-end ( Reporting Tools or Programming Languages ).

I used it here as I was using SQL Server Management Studio ( SSMS ) and querying the database directly and wanted to consume the query’s output verbatim in my document.

 

References

1. Microsoft | Docs
   • Docs / .NET / .NET fundamentals
     • Standard numeric format strings
       Link
     • Standard date and time format strings
       Link
     • Custom date and time format strings
       Link