Using Transact SQL Format Function

TransactSQLFormatFunction.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 
         [source] = 'raw'

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

        , [lifetimeEarnings]

        , [numberofShares]

from   @tblPerson tblP


select 
         [source] = '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 
         [source] = '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 
         [source] = '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