Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Using Transact SQL Format Function
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment