Date, time and number data types in SQL Server

• Published: 07 Feb 2007

SQL Server supports two T-SQL data types for storing date and time:

• datetime
• smalldatetime

The main difference between these two data types is in the amount of space they occupy. datetime occupies 8 bytes and smalldatetime only 4 bytes. Other differences between the two types are the precision of the date stored and the range of dates that can be used. The precision of smalldatetime is one minute, and it covers dates from January 1, 1900, through June 6, 2079, which is usually more than enough. The precision of datetime is 3.33 ms, and it covers dates from January 1, 1753, to December 31, 9999.

Date and time constants are written in Transact-SQL with quote delimiters (as are character strings):

update Contacts_2
Set DateOfBirth = '2/21/1965 10:03 AM'
where ContactId = 'CO-92-81'

TIP
SQL Server supports many different date and time formats. The Convert() function accepts a parameter that controls the format of date and time functions (explained in detail in Chapter 4).

If time is not specified in a constant, SQL Server automatically assigns a default value—12:00 A.M. (midnight). You should keep in mind that SQL Server always records time as a part of these data types. Thus, if you want to select all contacts born on a particular day, you should not use something like this:

select *
from Contacts_2
where DateOfBirth = '2/21/1965'

This statement would extract records with DateOfBirth set to midnight of that day. Such a solution might be acceptable if all other applications recording values in the field also make the same mistake. A proper solution would be

select *
from Contacts_2
where DateOfBirth >= '2/21/1965' and DateOfBirth < '2/22/1965'

Integer Numbers 
Integers are whole numbers. SQL Server supports 1- , 2- , 4- , and 8-byte integers. The bit data type is used to store 1 or 0, to represent logical true and false values. The following table lists integer data types, their storage size, and range of values.

The great thing about the int data types is that they can store huge numbers in a small space. For this reason, they are often used for key values. If the data type of the primary key is int, the table can store up to four billion records, which is typically enough for any purpose. Therefore, if you are not sure about the size of your table, use int as its primary key.

TIP
However, we are starting to see computers with billions of records—both OLTP and data warehousing systems are getting bigger and bigger, and there are also some implementations of distributed databases that can use integers higher than two billion. In those cases you could use bigint for primary keys.

Integer constants do not need delimiters:

update Inventory
Set StatusId = 3,
    Operational = 0
Where InventoryId = 3432

Approximate Numbers
Decimal numbers are often stored in real and float data types, also known as single and double precision. Their advantage is that they do not occupy much space but they can hold large ranges of numbers. The only trouble is that they are not exact. They store a binary representation of the number that is often approximately, but not exactly, equal to the original decimal number.

Precision is the number of significant digits in the number, and scale is the number of digits to the right of the decimal point. For example, the number 123456.789 has a precision of 9 and a scale of 3. The precision of real numbers is up to 7 digits, and the precision of float numbers is up to 15 digits. For this reason, they are ideal for science and engineering (where, for example, you may not care about a couple of meters when you are measuring the distance between the Earth and the Moon), but they are not adequate for the financial industry (where a company budget has to be exact to the last cent). To record the number 234,000,000,000 in mathematics, you can use 234×109, and in Transact-SQL, you can use 234E9. This is known as scientific notation. The number after E is called the exponent, and the number before E is called the mantissa. This notation can be used to store small constants, too. In mathematics, 0.000000000234 can be written as 0.234×10–9, and in Transact-SQL, it can be written as 0.234E-9.

SQL Server uses the IEEE 754 standard to store these numbers. When a float or real variable or column is assigned a number, SQL Server first converts the decimal number to its binary representation. This conversion is the reason these values are approximately, but not exactly, equal to the decimal version. This is why they are referred to as approximate numbers. Therefore, you should not rely on the equivalence of two such numbers. You should limit their use in Where clauses to < and > operators and avoid the use of the = operator.

Exact Numbers
The decimal or numeric data type does not use approximations when storing numbers. Unfortunately, it requires much more space than the real and float data types. When a decimal column or a variable is defined, you have to specify its scale and precision.
SQL Server can store decimal numbers with a maximum precision of 38. Scale can be less than or equal to the precision.
In the next example, Weight and Height columns have precision 5 and scale 2—the columns can have up to two digits after the decimal point and up to three digits before.

Create table Patient (PatientId int,
                      FullName varchar(30),
                      Weight decimal(5,2),
                      Height decimal(5,2),
                      ADP smallint,
                      BDZ tinyint)
go

decimal constants do not need delimiters either:

insert into Patient (PatientId, FullName, Weight, Height, ADP, BDZ)
values (834021, 'Tom Jones', 89.5, 188.5, 450, 11)

Monetary Data Types 
The money and smallmoney data types are a compromise between the precision of decimal numbers and the small size of real numbers. smallmoney occupies 4 bytes and uses the same internal structure as int numbers. The data can have up to four digits after the decimal point. For this reason, you can store numbers ranging from –214,768.3648 to 214,768.3647 in the smallmoney data type. The money data type uses the same structure for storing information as the bigint data type. It occupies 8 bytes for storage, so its values must range from –922,337,203,685,477.5808 to +922,337,203,685,477.5807.
Monetary constants can be preceded by $ or one of 18 other currency symbols (listed in SQL Server Books OnLine):

update Inventory_2
Set Rent = $0,
LeaseCost = $119.95
Where InventoryId = 3432

Binary Data Types
Binary data types are used to store strings of bits. SQL Server supports four basic binary data types, the attributes of which are similar to character data types:

• binary
• varbinary
• varbinary(max
• image

The binary and varbinary data types can store up to 8,000 bytes of information, and image and varbinary (max) can store up to 2GB of data. The following example creates a table that has two binary columns:

CREATE TABLE MyTable (
     Id int,
     BinData varbinary(8000),
     Diagram varbinary(max))
go

Binary constants are written as hexadecimal representations of bit strings and prefixed with 0x (zero and x):

Update MyTable
Set BinData = 0x82A7210B
where Id = 121131

A cool, new feature of SQL Server 2005 is the OPENROWSET() function with the new BULK OLE DB provider to read a file and load it as a rowset to a varbinary(max) column:
 

UPDATE dbo.MyTable
SET Diagram = (SELECT * 
               FROM OPENROWSET(BULK 'C:My Picturesdesktop.bmp', 
                               SINGLE_BLOB) AS a )
where Id = 121131

Basic Transact-SQL Programming Constructs

• Home: Introduction
• Tip 1: T-SQL identifiers
• Tip 2: Database object qualifiers
• Tip 3: Character string data types
• Tip4: Date, time and number data types
• Tip 5: Special data types-Part 1
• Tip 6: Special data types-Part 2
• Tip 7: Local variables
• Tip 8: Global variables
• Tip 9: Table variables
• Tip 10: Flow control statements
• Tip 11: Blocks, looping and conditional statements
• Tip 12: Unconditional and scheduled execution
• Tip 13: Transact SQL cursors
• Tip 14: Cursor related statements
• Tip 15: Problems and justified use of cursors

The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.