SQL Server Datatypes QuickRef

Datatypes
Family	name	since SQL#	bytes	lower bound	upper bound
Date/Time	date	2008	3	0001-01-01	9999-12-31
datetime		8	1753-01-01 00:00:00.000	9999-12-31 23:59:59.997
datetime2[(`s`)]	2008	`s` < 3/5/8 = 6/7/8	0001-01-01 00:00:00.0…	9999-12-31 23:59:59.9…
datetimeoffset	2008	10	0001-01-01 00:00:00.0000000 -14:00	9999-12-31 23:59:59.9999999 +14:00
smalldatetime		4	1900-01-01 00:00	2079-12-31 23:59
time	2008	5	00:00:00.0000000	23:59:59.9999999
Integer	bigint	2000	8	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
bit		1	0	1
int		4	-2,147,483,648	2,147,483,647
smallint		2	-32,768	32,767
tinyint		1	0	255
Monetary	money		8	-922,337,203,685,477.5808	922,337,203,685,477.5807
smallmoney		4	-214,748.3648	214,748.3647
Approximate Numeric	float[(`n`)]		8
real		4
Exact Numeric	decimal[(`p`[,`s`])]		`p` < 9/19/28/38 = 5/9/13/17
numeric[(`p`[,`s`])]
Binary	binary[(`n`)]		`n` ≤ 8,000
varbinary[(`n`)]		≤ `n` ≤ 8,000
Character	char[(`n`)]		`n` ≤ 8,000
nchar[(`n`)]	7	`n` × 2 ≤ 8,000
nvarchar[(`n`)]	7	≤ `n` × 2 ≤ 8,000
varchar[(`n`)]		≤ `n` ≤ 8,000
Long Text and Binary	image	dep. 2005	16 byte in-row pointer to ≤ 2,147,483,647 bytes data. Max types are stored in-row up to 8,000 bytes. n(var)char/ntext are stored as UCS-2, two bytes per character.
ntext	7, dep. 2005
nvarchar(max)	2005
text	dep. 2005
varbinary(max)	2005
varchar(max)	2005
Special	hierarchyid	2008	A slash-separated path: `/1/3/`
rowversion		8
sql_variant	2000	Stores any datatype except long text, long binary, or special types (max 8016 bytes).
table	2000	Sometimes faster than temporary tables. `declare @name table ( … table definition … )`
uniqueidentifier	7	16
xml	2005	16 (in-row pointer)	XML data with a schema is stored as a parsed tree.

Synonyms
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
timestamp	rowversion

Current Timestamp Variants
function	datatype	detail
current_timestamp	datetime	ANSI SQL standard equivalent of getdate()
{fn current_date()}	varchar(10)	ODBC cannonical system date
{fn current_time()}	varchar(30)	ODBC cannonical system time
{fn now()}	datetime	ODBC cannonical equivalent of getdate()
getdate()	datetime	current system date and time
getutcdate()	datetime	current UTC date and time
sysdatetime()	datetime2(7)	current system date and time
sysdatetimeoffset()	datetimeoffset(7)	current date and time with timezone
sysutcdatetime()	datetime2	current UTC date and time

convert(varchar,`datetime`,`n`)
code	2-digit year code	Format	Name
0 or 100		MMM dd yyyy hh:mmtt	Default
101	1	MM/dd/yyyy	US
102	2	yyyy.MM.dd	ANSI
103	3	dd/MM/yyyy	British/French
104	4	dd.MM.yyyy	German
105	5	dd-MM-yyyy	Italian
106	6	dd MMM yyyy
107	7	MMM dd, yyyy
8 or 108		HH:mm:ss
9 or 109		MMM dd yyyy hh:mm:ss.fff…tt
110	10	MM-dd-yyyy	US
111	11	yyyy/MM/dd	Japan
112	12	yyyyMMdd	ISO
13 or 113		dd MMM yyyy HH:mm:ss.fff…	Europe w/ms
14 or 114		HH:mm:ss:fff…
20 or 120		yyyy-MM-dd HH:mm:ss	ODBC
21 or 121		yyyy-MM-dd HH:mm:ss.fff…	ODBC w/ms
126		yyyy-MM-ddTHH:mm:ss.fff…	ISO8601
127		yyyy-MM-ddTHH:mm:ss.fff…Z	ISO8601 w/timezone Z
130		dd MMM yyyy hh:mm:ss.fff…tt	Hijri
131		dd/MM/yyyy hh:mm:ss.fff…tt	Hijri
MMM = Jan, …, Dec; tt = AM or PM; T = T HH = 24-hour hours, fff… = all fractional seconds digits available `cast(datetime as varchar)` ≡ `convert(varchar,datetime,0)` `time_at_new_zone= switchoffset(datetimeoffset,'±HH:mm')` `string_name= {fn dayname(datetime)}` `string_name= {fn monthname(datetime)}` `int_quarter= {fn quarter(datetime)}`

Date Parts
date part	abbrev	lower bound	upper bound
year	yy or yyyy	1	9999
quarter	q or qq	1	4
month	m or mm	1	12
dayofyear	y or dy	1	366
day	d or dd	1	31
week (configurable)	ww or wk	1	53
weekday	dw	1 (Sun)	7 (Sat)
hour	hh	0	23
minute	n or mi	0	59
second	s or ss	0	59
millisecond	ms	0	999
microsecond	mcs	0	999999
nanosecond	ns	0	999999900
tzoffset	tz	-840	840
iso_week (thursday-week)	isowk or isoww	1	53
millisecond	ms	0	999
`datetimish_new = dateadd(datepart,number,datetimish)` `datetimish_new = date_bucket(datepart,number,datetimish, datetimish_origin = '1900-01-01')` _v2022 `int_diff = datediff(datepart,from_datetimish,to_datetimish)` `bigint_diff = datediff_big(datepart,from_datetimish,to_datetimish)` `nvarchar_name = datename(datepart,datetimish)` `int_value = datepart(datepart,datetimish)` `datetimish_new = datetrunc(datepart,datetimish)` _v2022 `datetimish` is `date`, `datetime`, `datetime2`, `datetimeoffset`, `smalldatetime`, or `time` (output type matches input type).