Datatypes
Familynamesince SQL#byteslower boundupper bound
Date/Timedate200830001-01-019999-12-31
datetime81753-01-01 00:00:00.0009999-12-31 23:59:59.997
datetime2[(s)]2008s < 3/5/8 = 6/7/80001-01-01 00:00:00.0…9999-12-31 23:59:59.9…
datetimeoffset2008100001-01-01 00:00:00.0000000 -14:009999-12-31 23:59:59.9999999 +14:00
smalldatetime41900-01-01 00:002079-12-31 23:59
time2008500:00:00.000000023:59:59.9999999
Integerbigint20008-9,223,372,036,854,775,8089,223,372,036,854,775,807
bit101
int4-2,147,483,6482,147,483,647
smallint2-32,76832,767
tinyint10255
Monetarymoney8-922,337,203,685,477.5808922,337,203,685,477.5807
smallmoney4-214,748.3648214,748.3647
Approximate Numericfloat[(n)]8
real4
Exact Numericdecimal[(p[,s])]p < 9/19/28/38 = 5/9/13/17
numeric[(p[,s])]
Binarybinary[(n)]n ≤ 8,000
varbinary[(n)]n ≤ 8,000
Characterchar[(n)]n ≤ 8,000
nchar[(n)]7n × 2 ≤ 8,000
nvarchar[(n)]7n × 2 ≤ 8,000
varchar[(n)]n ≤ 8,000
Long Text and Binaryimagedep. 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.
ntext7, dep. 2005
nvarchar(max)2005
textdep. 2005
varbinary(max)2005
varchar(max)2005
Specialhierarchyid2008A slash-separated path: /1/3/
rowversion8
sql_variant2000Stores any datatype except long text, long binary, or special types (max 8016 bytes).
table2000Sometimes faster than temporary tables. declare @name table ( … table definition … )
uniqueidentifier716
xml200516 (in-row pointer)XML data with a schema is stored as a parsed tree.
Synonyms
synonymdatatype
binary varyingvarbinary
[national] character[(n)][n]char[(n)]
[national] character varying(n)[n]varchar(n)
[national] textntext
decdecimal
integerint
double precisionfloat
float(1-7)real
float(8-15)float
timestamprowversion
Current Timestamp Variants
functiondatatypedetail
current_timestampdatetimeANSI 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()}datetimeODBC cannonical equivalent of getdate()
getdate()datetimecurrent system date and time
getutcdate()datetimecurrent UTC date and time
sysdatetime()datetime2(7)current system date and time
sysdatetimeoffset()datetimeoffset(7)current date and time with timezone
sysutcdatetime()datetime2current UTC date and time
convert(varchar,datetime,n)
code2-digit
year
code
FormatName
0 or 100MMM dd yyyy hh:mmttDefault
1011MM/dd/yyyyUS
1022yyyy.MM.ddANSI
1033dd/MM/yyyyBritish/French
1044dd.MM.yyyyGerman
1055dd-MM-yyyyItalian
1066dd MMM yyyy
1077MMM dd, yyyy
8 or 108HH:mm:ss
9 or 109MMM dd yyyy hh:mm:ss.fff…tt
11010MM-dd-yyyyUS
11111yyyy/MM/ddJapan
11212yyyyMMddISO
13 or 113dd MMM yyyy HH:mm:ss.fff…Europe w/ms
14 or 114HH:mm:ss:fff…
20 or 120yyyy-MM-dd HH:mm:ssODBC
21 or 121yyyy-MM-dd HH:mm:ss.fff…ODBC w/ms
126yyyy-MM-ddTHH:mm:ss.fff…ISO8601
127yyyy-MM-ddTHH:mm:ss.fff…ZISO8601 w/timezone Z
130dd MMM yyyy hh:mm:ss.fff…ttHijri
131dd/MM/yyyy hh:mm:ss.fff…ttHijri
  • 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 partabbrevlower boundupper bound
yearyy or yyyy19999
quarterq or qq14
monthm or mm112
dayofyeary or dy1366
dayd or dd131
week (configurable)ww or wk153
weekdaydw1 (Sun)7 (Sat)
hourhh023
minuten or mi059
seconds or ss059
millisecondms0999
microsecondmcs0999999
nanosecondns0999999900
tzoffsettz-840840
iso_week (thursday-week)isowk or isoww153
millisecondms0999
  • 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).