TSQL Data Types

Below is a list of all of the available data types in TSQL and their relevant attributes.

Exact Numbers

Field Type	Parameters
bigint	Range: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) Space: 8 Bytes
int	Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) Space: 4 Bytes
smallint	Range: -2^15 (-32,768) to 2^15-1 (32,767) Space: 2 Bytes
tinyint	Range: 0 to 255 Space: 1 Byte
bit	Range: 0 (FALSE) or 1 (TRUE) Space: 8 bit columns in a table, will be collectively stored as: 1 Byte 9 - 16 bit columns in a table, will be collectively stored as: 2 Bytes, etc.
decimal	Declaration: decimal(p[,s]).  p = Precision - total number of digits stored to both the left and right of the decimal point. s = Scale the maximum number of digits stored to the right of the decimal point (optional). Precision 1 - 9: Storage is 5 bytes Precision 10 - 19: Storage is 9 bytes Precision 20 - 28: Storage is 13 bytes Precision 29 - 38: Storage is 17 bytes Minimum Precision is 1 and Maximum Precision is 38. The Default Precision is 18. Note: Decimal is equivalent to Numeric.
numeric	Declaration: numeric(p[,s]).  p = Precision - total number of digits stored to both the left and right of the decimal point. s = Scale the maximum number of digits stored to the right of the decimal point (optional). Precision 1 - 9: Storage is 5 bytes Precision 10 - 19: Storage is 9 bytes Precision 20 - 28: Storage is 13 bytes Precision 29 - 38: Storage is 17 bytes Minimum Precision is 1 and Maximum Precision is 38. The Default Precision is 18. Note: Numeric is equivalent to Decimal.
money	Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807 Space: 8 bytes
smallmoney	Range: -214,748.3648 to 214,748.3647 Space: 4 bytes

Approximate Numbers

Field Type	Parameters
float	Declaration: float(n).  n = the number of bits used to store the floating point number. Range: -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 n Value 1 - 24: Precision - 7 digits: Space - 4 bytes n Value 25 - 53: Precision - 15 digits: Space - 8 bytes
real	Range: -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 Space: 4 bytes Note: Real is equivalent to float(24).

Date and Time

Field Type	Parameters
datetime	Range: January 1, 1753, through December 31, 9999 Accuracy: 3.33 ms Space: 8 bytes (two 4 byte integers). First 4 bytes represent the number of days before or after Jan. 1, 1900. The Second 4 bytes store the time of day as a number 1/3000-second units after 12:00 AM (00:00:00).
smalldatetime	Range: January 1, 1900, through June 6, 2079 Accuracy: 1 min Space: 4 bytes (two 2 byte integers). First 2 bytes represent the number of days after Jan. 1, 1900. The Second 2 bytes store the number of minutes after 12:00 AM (00:00:00).

Character Strings

Field Type	Parameters
char	Definition: Fixed-Length character string. Declaration: char(n).  n = the number of characters. Space: n number of bytes Valid lengths for a char datatype are 1 through 8,000.
varchar	Definition: Variable-Length character string. Declaration: varchar(n | max).  n = the number of characters. Space: characters actually used in datatype (1 byte per character) + 2 additional bytes Valid lengths for a varchar datatype are 1 through 8,000. Alternatively, MAX allows for a much larger maximum storage size (2^31-1 bytes - 2,147,483,647 characters).
text	Definition: Variable-Length character string in the code page of the server. Maximum length is 2,147,483,647 characters. Note: will be removed in future versions. Use varchar(max) instead.

Unicode Character Strings

Field Type	Parameters
nchar	Definition: Fixed-Length Unicode character string. Declaration: nchar(n).  n = the number of characters. Space: n * 2 number of bytes Valid lengths for a char datatype are 1 through 4,000.
nvarchar	Definition: Variable-Length Unicode character string. Declaration: nvarchar(n | max).  n = the number of characters. Space: characters actually used in datatype (2 bytes per character) + 2 additional bytes Valid lengths for a varchar datatype are 1 through 4,000. Alternatively MAX indicates that the maximum storage size is much larger (2^31-1 bytes - 2,147,483,647 characters).
ntext	Definition: Variable-Length character string in the code page of the server. Maximum length is 1,073,741,823 characters. Note: will be removed in future versions. Use nvarchar(max) instead.

Binary Strings

Field Type	Parameters
binary	Definition: Fixed-Length binary data. Declaration: binary(n).  Space: n number of bytes. Maximum length is 8000 bytes.
varbinary	Definition: Variable-Length binary data. Declaration: varbinary(n | max).  n = the number of characters. Space: actual number of bytes stored in datatype + 2 additional bytes Valid lengths for a varbinary datatype are 1 through 8,000. Alternatively, MAX allows for a much larger maximum storage size (2^31-1 bytes - 2,147,483,647 bytes).
image	Definition: Variable-Length binary data. Maximum length is 2,147,483,647 bytes. Note: will be removed in future versions. Use varbinary(max) instead.

Other Data Types

• cursor
• sql_variant
• table
• timestamp
• uniqueidentifier
• xml

The many faces of Datetime

Image via Wikipedia

As report writing professionals we, on a daily basis, have Ugo Boncompagni aka Pope Gregory XIII  to thank for a consistent and repeating annoyance.  Popes over the years have been attributed to bringing many weird and wonderful revelations to the world, this one in particular landed us with our calendar.  Good old Ugo.

Given that it has been knocking around since 1582, it's not doing too bad a job.  In fact it behaved itself for around 388 years, which is when things went wrong.  Fast forward to the beginning of the 1970s, IBM, no doubt binding dark forces together, developed a new computer language called SQL (known as SEQUEL at the time - but had to change it to avoid trademark infringement).

As Mr Boncompagni's calendar was slowly adopted across the globe, there were some countries who just had to be different.  You know the type of countries - those who never tucked their school shirts in and wore their tie all wrong.  So today, although we are using the same calendar, we are left with a dozen ways to write a date depending on where you are stood.

'So - what has that got to do with me?' I hear you asking.

If you are working on one or more databases that handle dates which only handles data relevant for your own country, and the database environment has been installed with all date related default settings aligned correctly then you should (mostly) be fine and don't need to read further. However, if you find yourselves working on international data then you may find something of interest.

Randomly picking a date from the calendar, say 5th April, you can write it:

Type	Format
Long Date	5th, April 2010
Short Date	5th Apr 2010
British/French	5/4/2010
U.S.	4/5/2010
Japan	10/4/5

And a number of other ways also.

As you can see - a date can quickly lack any meaning depending on how it is presented.  As a preference I prefer using the long date, as it removes any question of doubt (defaulting to the short date if space is an issue).  However, opinions differ and more often than not you will need to present it as dd/mm/yy or yy/mm/dd etc.

In the aforementioned SQL, since MS SQL 2000, you are able to use the CONVERT function to handle your date formatting.

SYNTAX
======
CONVERT( {data_type} [ (length) ] , expression [ ,style ] )
ie
SELECT CONVERT(nvarchar(20) ,getdate() ,103)

The style argument is the one of interest to us.  The resultant data_type will have the date formatted as dictated by the style.  Using this function is a rather effective way to remove the time part from a datatime.

Without century (yy) (1)	With century (yyyy)	Standard	Input/Output (3)
-	0 or 100 (1,2)	Default	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yyyy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106(1)	-	dd mon yy
7	107(1)	-	Mon dd, yy
8	108	-	hh:mi:ss
-	9 or 109 (1,2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd yyyymmdd
-	13 or 113 (1,2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
-	127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
-	130 (1,2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	Hijri (5)	dd/mm/yy hh:mi:ss:mmmAM

There is also a CAST function, however you can not handle styles with it - so is not much use here.

So, say you have a datetime

SELECT CONVERT(datetime,'2010-06-17 01:23:45.000') as myDate

Gives you:

myDate
2010-06-17 01:23:45.000

However, you don't want the time and you want it in, say British/French format

SELECT CONVERT(nvarchar(20),CONVERT(datetime,'2010-06-17 00:00:00.000'),103) as myDate

Gives you:

myDate
17/06/2010

This proves particularly useful if you need to summarise by a date and have several times that need to be grouped together.

Another way to achieve the removal of a timestamp from datetime is :

select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

which is also a popular and fast way of doing it.