MS SQL DataTypes QuickRef

SQL Server Datatypes

Family name ADO Constant (reported) bytes lower bound upper bound

Integer bigint₂₀₀₀ adBigInt=20 8 -9,223,372,036,854,775,808 9,223,372,036,854,775,807

int adInteger=3 4 -2,147,483,648 2,147,483,647

smallint adSmallInt=2 2 -32,768 32,767

tinyint adUnsignedTinyInt=17 1 0 255

Monetary money adCurrency=6 8 -922,337,203,685,477.5808 922,337,203,685,477.5807

smallmoney adCurrency=6 4 -214,748.3648 214,748.3647

Date/Time datetime adDBTimeStamp=135 8 Jan 1, 1753 Dec 31, 9999

smalldatetime adDBTimeStamp=135 4 Jan 1, 1900 Dec 31, 2079

Special bit adBoolean=11 1 0 1

timestamp

adBinary=128

synonym	datatype
binary varying	varbinary
[national] character[(n)]	[n]char[(n)]
[national] character varying(n)	[n]varchar(n)
[national] text	ntext
dec	decimal
integer	int
double precision	float
float(1-7)	real
float(8-15)	float

sql_variant₂₀₀₀

sql_variant stores any datatype except text, ntext, image, timestamp, and sql_variant (max 8016 bytes, adVarBinary=204).

uniqueidentifier₇ adGUID=72 16

Approximate Numeric float[(n)] adDouble=5 8

real adSingle=4 4

Exact Numeric decimal[(p[,s])] adDecimal=14 (adNumeric=131) p < 9/19/28/38 =5/9/13/17

numeric[(p[,s])] adNumeric=131

Binary binary[(n)] adBinary=128 n

varbinary[(n)] adVarBinary=204 (adBinary=128) m <= n

Character char[(n)] adChar=129 n

nchar[(n)]₇ adWChar=130 n*2

nvarchar[(n)]₇ adVarWChar=202 (adWChar=130) m <= n*2

varchar[(n)] adVarChar=200 (adChar=129) m <= n

Text and Image text adLongVarChar=201 (adChar=129) <= 2,147,483,647

ntext₇ adLongVarWChar=203 (adWChar=130)

image adLongVarBinary=205 (adBinary=128)

Columns

Date Conversion

#	Format
100*	mon dd yyyy hh:miAM
101	mm/dd/yyyy
102	yyyy.mm.dd
103	dd/mm/yyyy
104	dd.mm.yyyy
105	dd-mm-yyyy
106	dd mon yyyy
107	mon dd, yyyy
108	hh:mm:ss
109*	mon dd yyyy hh:mi:ss:mmmAM
110	mm-dd-yyyy
111	yyyy/mm/dd
112	yyyymmdd
113*	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
*2-digit year unavailable

Date Parts

date part	TSQL abbrev	VBScript abbrev	lower bound	upper bound
year	yy	yyyy	1753	9999
quarter	qq	q	1	4
month	mm	m	1	12
day of year	dy	y	1	366
day	dd	d	1	31
week	wk	ww	1	53
weekday	dw	w	1 (Sun)	7 (Sat)
hour	hh	h	0	23
minute	mi	n	0	59
second	ss	s	0	59
millisecond	ms	`-`	0	999
`datetime_new`= `dateadd`(`datepart`,`number`,`datetime`)
`number_diff`= `datediff`(`datepart`,`from_datetime`,`to_datetime`)
`string_name`= `datename`(`datepart`,`datetime`)
`number_value`= `datepart`(`datepart`,`datetime`)

ADO `OpenSchema` `adSchemaColumns`=4

The ADO OpenSchema method returns incomplete info in the DATA_TYPE column for many data types.

Variable length columns are represented by fixed-length types, with the adFldFixed=16 flag in COLUMN_FLAGS clear.
Long fields have the adFldLong=128 flag set in COLUMN_FLAGS.
The smallmoney NUMERIC_PRECISION is less than 19, distinguishing it from money.
The smalldatetime datatype cannot be distinguished from datetime (but the former is of limited usefulness given the range, so is perhaps best avoided).
Defaults are indicated by COLUMN_HASDEFAULT and available in COLUMN_DEFAULT.
Character (and binary) field lengths are given in CHARACTER_MAXIMUM_LENGTH.
CHARACTER_OCTET_LENGTH contains the field's length in bytes.
Numeric column precision and scale are given by NUMERIC_PRECISION and NUMERIC_SCALE, respectively.
Finally, IS_NULLABLE denotes whether the field may contain null values.

ADO `OpenSchema` `adSchemaProcedureParameters`=26