Database Reference
In-Depth Information
Converting the Data Types
Another transformation is data type conversion between the source and destination tables. Listing 6-8 shows SQL
code that converts the data types as the data is selected from the source table. This is a very simple and fast way of
performing these types of transformations.
Listing 6-8.
Using the Function to Convert Data Types
Select
[TitleId]=
Cast
( [title_id]
as nvarchar(6) )
, [TitleName]=
Cast
( [title]
as nvarchar(50) )
, [TitleType]=
Cast
( [type]
as nvarchar(50) )
--, [pub_id] Will be replaced with a PublisherKey
, [TitlePrice]=
Cast
( [price]
as decimal(18, 4) )
, [PublishedDate]=[pubdate] -- has the same data type in both tables
From [Pubs].[dbo].[Titles]
If you have programmed in SQL before, you may have noted that this listing utilizes the
CAST()
function to
change existing data types from the source table. The cast function is one of the oldest and simplest functions
in Microsoft SQL Server. It has a sister function called
CONVERT()
that can also perform these conversions. The
CONVERT()
function has additional options that are not available in
CAST()
. One example of this is how the
CONVERT()
function can be used to change the format of the date. Figure
6-3
shows an example.
Figure 6-3.
Using the
CONVERT()
function with additional formatting options
