Data Types and Conversion

Categories of Data Types

Exact Numerics
Approximate Numerics
Date and Time
Character Strings
Unicode Character Strings
Binary Strings
Other Data Types

Exact Numerics - Integers

Numbers without a decimal point

Data Type	Storage
bigint	8 bytes
int	4 bytes
smallint	2 bytes
tinyint	1 byte

Exact Numerics - Decimals

Untitled

Approximate Numerics

Float
Real
Store approximate numeric values

Date and Time Data Types

Data Type	Format	Accuracy
time	`hh:mm:ss[.nnnnnnn]`	100 nanoseconds
date	`YYYY-MM-DD`	1 day
smalldatetime	`YYYY-MM-DD hh:mm:ss`	1 minute
datetime	`YYYY-MM-DD hh:mm:ss[.nnn]`	0.00333 second
datetime2	`YYYY-MM-DD hh:mm:ss[.nnnnnnn]`	100 nanoseconds

Character and Unicode Character Data Types

Character Data Types store character strings (ASCII)
- char
- varchar
- text
Unicode Data Types are used for storing Unicode data (non-ASCII)
- nchar
- nvarchar
- ntext

Other Data Types

binary
image
cursor
rowversion
uniqueidentifier
xml
Spatial Geometry / Geography Types

Implicit Conversion

Data Comparison

Keep in mind : for comparing two values, they need to be of the same type. Otherwise:
- SQL Server converts from one type to another (IMPLICIT)
- The developer explicitly converts the data (EXPLICIT)

Data Type Precedence

user-defined data types (highest)
datetime
date
float
decimal
int
bit
nvarchar (including nvarchar(max))
varchar (including varchar(max))
binary (lowest)

Implicit Conversion Between Data Types

Untitled

Performance Impact of Implicit Conversion

Implicit conversion is done for each row of the query
Implicit conversion can be prevended with a good database schema design.

Explicit Conversion

Implicit and Explicit Conversion

IMPLICIT : performed automatically, behind the scenes
EXPLICIT : performed with functions CAST() and CONVERT()
- CAST() and CONVERT() are used to convert from one data type to another

CAST()

<aside> 💡 Syntax: CAST(expression AS data_type[(length)])

</aside>

SELECT
          CAST(3.14 AS INT) AS DECIMAL_TO_INT,
          CAST('3.14' AS DECIMAL(3,2)) AS STRING_TO_DECIMAL,
          CAST(GETDATE() AS NVARCHAR(20)) AS DATE_TO_STRING,
          CAST(GETDATE() AS FLOAT) AS DATE_TO_FLOAT;

CONVERT()

<aside> 💡 Syntax: CONVERT(data_type [(length)], expression, [,style])

</aside>

SELECT
          CONVERT(INT, 3.14) AS DECIMAL_TO_INT,
          CONVERT(DECIMAL(3,2), '3.14') AS STRING_TO_DECIMAL,
          CONVERT(NVARCHAR(20), GETDATE(), 104) AS DATE_TO_STRING,
          CONVERT(FLOAT, GETDATE()) AS DATE_TO_FLOAT;

CAST() vs CONVERT()

Functions that return system date and time

Common mistakes when working with dates and time

Inconsistent date time formats or patterns
Arithmetic Operations
Issues with time zones

Time Zones in SQL Server

Local Time Zone
UTC Time Zone ( Universal Time Coordinate )