Data Types

Table of Contents
Overview
Constants
Conversion
Precedence
Synonyms
Precision, Scale, and Length
bit
cursor
Date and time
date
datetime
datetime2
datetimeoffset
smalldatetime
time
hierarchyid methods
GetAncestor (Database Engine)
GetDescendant (Database Engine)
GetLevel (Database Engine)
GetRoot (Database Engine)
IsDescendantOf (Database Engine)
Parse (Database Engine)
Read (Database Engine)
GetReparentedValue (Database Engine)
ToString (Database Engine)
Write (Database Engine)
Numeric
decimal and numeric
float and real
int, bigint, smallint, and tinyint
money and smallmoney
rowversion
String and binary
binary and varbinary
char and varchar
nchar and nvarchar
ntext, text, and image
Spatial geography
Spatial geometry
sql_variant
Table
uniqueidentifier
XML
Data types (Transact-SQL)
5/3/2018 • 2 min to read • Edit Online
THIS TOPIC APPLIES TO: SQL Server (starting with 2008) Azure SQL Database Azure SQL Data
Warehouse Parallel Data Warehouse
In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an
attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date
and time data, binary strings, and so on.
SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server.
You can also define your own data types in Transact-SQL or the Microsoft .NET Framework. Alias data types are
based on the system-supplied data types. For more information about alias data types, see CREATE TYPE
(Transact-SQL ). User-defined types obtain their characteristics from the methods and operators of a class that
you create by using one of the programming languages support by the .NET Framework.
When two expressions that have different data types, collations, precision, scale, or length are combined by an
operator, the characteristics of result are determined by the following:
The data type of the result is determined by applying the rules of data type precedence to the data types of
the input expressions. For more information, see Data Type Precedence (Transact-SQL ).
The collation of the result is determined by the rules of collation precedence when the result data type is char,
varchar, text, nchar, nvarchar, or ntext. For more information, see Collation Precedence (Transact-SQL ).
The precision, scale, and length of the result depend on the precision, scale, and length of the input
expressions. For more information, see Precision, Scale, and Length (Transact-SQL ).
SQL Server provides data type synonyms for ISO compatibility. For more information, see Data Type Synonyms
(Transact-SQL ).
Data type categories

Data types in SQL Server are organized into the following categories:
Exact numerics Unicode character strings
Approximate numerics Binary strings
Date and time Other data types
Character strings
In SQL Server, based on their storage characteristics, some data types are designated as belonging to the
following groups:
Large value data types: varchar(max), and nvarchar(max)
Large object data types: text, ntext, image, varbinary(max), and xml
NOTE
sp_help returns -1 as the length for the large-value and xml data types.
Exact numerics
bigint numeric
bit smallint
decimal smallmoney
int tinyint
money
Approximate numerics
float real
Date and time
date datetimeoffset
datetime2 smalldatetime
datetime time
Character strings
char varchar
text
Unicode character strings
nchar nvarchar
ntext
Binary strings
binary varbinary
image
Other data types
cursor rowversion
hierarchyid uniqueidentifier
sql_variant xml
Spatial Geometry Types Spatial Geography Types
table
See also
CREATE PROCEDURE (Transact-SQL )
CREATE TABLE (Transact-SQL )
DECL ARE @local_variable (Transact-SQL ) EXECUTE (Transact-SQL )
Expressions (Transact-SQL )
Functions (Transact-SQL )
LIKE (Transact-SQL )
sp_droptype (Transact-SQL )
sp_help (Transact-SQL )
sp_rename (Transact-SQL )
Constants (Transact-SQL)
A constant, also known as a literal or a scalar value, is a symbol that represents a specific data value. The format of
a constant depends on the data type of the value it represents.
Character string constants

Character string constants are enclosed in single quotation marks and include alphanumeric characters (a-z, A-Z,
and 0-9) and special characters, such as exclamation point (!), at sign (@), and number sign (#). Character string
constants are assigned the default collation of the current database, unless the COLL ATE clause is used to specify a
collation. Character strings typed by users are evaluated through the code page of the computer and are translated
to the database default code page if it is required.
If the QUOTED_IDENTIFIER option has been set OFF for a connection, character strings can also be enclosed in
double quotation marks, but the Microsoft SQL Server Native Client Provider and ODBC driver automatically use
SET QUOTED_IDENTIFIER ON. We recommend using single quotation marks.
If a character string enclosed in single quotation marks contains an embedded quotation mark, represent the
embedded single quotation mark with two single quotation marks. This is not required in strings embedded in
double quotation marks.
The following are examples of character strings:
'Cincinnati'
'O''Brien'
'Process X is 50% complete.'
'The level for job_id: %d should be between %d and %d.'
"O'Brien"
Empty strings are represented as two single quotation marks with nothing in between. In 6.x compatibility mode,
an empty string is treated as a single space.
Character string constants support enhanced collations.
NOTE
Character constants greater than 8000 bytes are typed as varchar(max) data.
Unicode strings
Unicode strings have a format similar to character strings but are preceded by an N identifier (N stands for
National Language in the SQL -92 standard). The N prefix must be uppercase. For example, 'Michél' is a character
constant while N'Michél' is a Unicode constant. Unicode constants are interpreted as Unicode data, and are not
evaluated by using a code page. Unicode constants do have a collation. This collation primarily controls
comparisons and case sensitivity. Unicode constants are assigned the default collation of the current database,
unless the COLL ATE clause is used to specify a collation. Unicode data is stored by using 2 bytes per character
instead of 1 byte per character for character data. For more information, see Collation and Unicode Support.
Unicode string constants support enhanced collations.
NOTE
Unicode constants greater than 8000 bytes are typed as nvarchar(max) data.
Binary constants
Binary constants have the prefix 0x and are a string of hexadecimal numbers. They are not enclosed in quotation
marks.
The following are examples of binary strings are:
0xAE
0x12Ef
0x69048AEFDD010E
0x (empty binary string)
NOTE
Binary constants greater than 8000 bytes are typed as varbinary(max) data.
bit constants
bit constants are represented by the numbers 0 or 1, and are not enclosed in quotation marks. If a number larger
than one is used, it is converted to one.
datetime constants
datetime constants are represented by using character date values in specific formats, enclosed in single quotation
marks.
The following are examples of datetime constants:
'December 5, 1985'
'5 December, 1985'
'851205'
'12/5/98'
Examples of datetime constants are:
'14:30:24'
'04:24 PM'
integer constants
integer constants are represented by a string of numbers that are not enclosed in quotation marks and do not
contain decimal points. integer constants must be whole numbers; they cannot contain decimals.
The following are examples of integer constants:
1894
2
decimal constants
decimal constants are represented by a string of numbers that are not enclosed in quotation marks and contain a
decimal point.
The following are examples of decimal constants:
1894.1204
2.0
float and real constants

float and real constants are represented by using scientific notation.
The following are examples of float or real values:
101.5E5
0.5E-2
money constants
money constants are represented as string of numbers with an optional decimal point and an optional currency
symbol as a prefix. money constantsare not enclosed in quotation marks.
SQL Server does not enforce any kind of grouping rules such as inserting a comma (,) every three digits in strings
that represent money.
NOTE
Commas are ignored anywhere in the specified money literal.
The following are examples of money constants:
$12
$542023.14
uniqueidentifier constants
uniqueidentifier constants are a string representing a GUID. They can be specified in either a character or binary
string format.
The following examples both specify the same GUID:
'6F9619FF-8B86-D011-B42D-00C04FC964FF'
0xff19966f868b11d0b42d00c04fc964ff
Specifying Negative and Positive Numbers

To indicate whether a number is positive or negative, apply the + or - unary operators to a numeric constant. This
creates a numeric expression that represents the signed numeric value. Numeric constants use positive when the +
or - unary operators are not applied.
Signed integer expressions:
+145345234
-2147483648
Signed decimal expressions:
+145345234.2234
-2147483648.10
Signed float expressions:
+123E-3
-12E5
Signed money expressions:
-$45.56
+$423456.99
Enhanced Collations
SQL Server supports character and Unicode string constants that support enhanced collations. For more
information, see the COLL ATE (Transact-SQL ) clause.
See also
Data Types (Transact-SQL )
Operators (Transact-SQL )
Data type conversion (Database Engine)
Data types can be converted in the following scenarios:
When data from one object is moved to, compared with, or combined with data from another object, the data
may have to be converted from the data type of one object to the data type of the other.
When data from a Transact-SQL result column, return code, or output parameter is moved into a program
variable, the data must be converted from the SQL Server system data type to the data type of the variable.
When you convert between an application variable and a SQL Server result set column, return code, parameter, or
parameter marker, the supported data type conversions are defined by the database API.
Implicit and explicit conversion

Data types can be converted either implicitly or explicitly.
Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to
another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the
comparison proceeds.
GETDATE () implicitly converts to date style 0. SYSDATETIME () implicitly converts to date style 21.
Explicit conversions use the CAST or CONVERT functions.
The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one
data type to another. For example, the following CAST function converts the numeric value of $157.27 into a
character string of '157.27' :
CAST ( $157.27 AS VARCHAR(10) )
Use CAST instead of CONVERT if you want Transact-SQL program code to comply with ISO. Use CONVERT
instead of CAST to take advantage of the style functionality in CONVERT.
The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server
system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on
assignment from the sql_variant data type, but there is implicit conversion to sql_variant.
Data type conversion behaviors
Some implicit and explicit data type conversions are not supported when you are converting the data type of one
SQL Server object to another. For example, an nchar value cannot be converted to an image value. An nchar can
only be converted to binary by using explicit conversion, an implicit conversion to binary is not supported.
However, an nchar can be explicitly or implicitly converted to nvarchar.
The following topics describe the conversion behaviors exhibited by their corresponding data types:
binary and varbinary (Transact-SQL )
datetime2 (Transact-SQL )
money and smallmoney (Transact-SQL )
bit (Transact-SQL )
datetimeoffset (Transact-SQL )
smalldatetime (Transact-SQL )
char and varchar (Transact-SQL )
decimal and numeric (Transact-SQL )
sql_variant (Transact-SQL )
date (Transact-SQL )
float and real (Transact-SQL )
time (Transact-SQL )
datetime (Transact-SQL )
int, bigint, smallint, and tinyint (Transact-SQL )
uniqueidentifier (Transact-SQL )
Converting Data Types by Using OLE Automation Stored Procedures
Because SQL Server uses Transact-SQL data types and OLE Automation uses Visual Basic data types, the OLE
Automation stored procedures must convert the data that passes between them.
The following table describes SQL Server to Visual Basic data type conversions.
SQL SERVER DATA TYPE VISUAL BASIC DATA TYPE
char, varchar, text, nvarchar, ntext String
decimal, numeric String
bit Boolean
binary, varbinary, image One-dimensional Byte() array
int Long
smallint Integer
tinyint Byte
float Double
real Single
money, smallmoney Currency
datetime, smalldatetime Date
Anything set to NULL Variant set to Null
All single SQL Server values are converted to a single Visual Basic value with the exception of binary, varbinary,
and image values. These values are converted to a one-dimensional Byte() array in Visual Basic. This array has a
range of Byte(0 to length1) where length is the number of bytes in the SQL Server binary, varbinary, or image
values.
These are the conversions from Visual Basic data types to SQL Server data types.
VISUAL BASIC DATA TYPE SQL SERVER DATA TYPE
Long, Integer, Byte, Boolean, Object int
Double, Single float

VISUAL BASIC DATA TYPE SQL SERVER DATA TYPE
Currency money
Date datetime
String with 4000 characters or less varchar/nvarchar
String with more than 4000 characters text/ntext
One-dimensional Byte() array with 8000 bytes or less varbinary
One-dimensional Byte() array with more than 8000 bytes image
See also
OLE Automation Stored Procedures (Transact-SQL )
CAST and CONVERT (Transact-SQL )
COLL ATE (Transact-SQL )
Data type precedence (Transact-SQL)
When an operator combines two expressions of different data types, the rules for data type precedence specify that
the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion
is not a supported implicit conversion, an error is returned. When both operand expressions have the same data
type, the result of the operation has that data type.
SQL Server uses the following precedence order for data types:
1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)
See also
Data type synonyms (Transact-SQL)
Data type synonyms are included in SQL Server for ISO compatibility. The following table lists the synonyms and
the SQL Server system data types that they map to.
SYNONYM SQL SERVER SYSTEM DATA TYPE
Binary varying varbinary
char varying varchar
character char
character char(1)
character( n ) char(n)
character varying( n ) varchar(n)
Dec decimal
Double precision float
float[(n)] for n = 1-7 real
float[(n)] for n = 8-15 float
integer int
national character( n ) nchar(n)
national char( n ) nchar(n)
national character varying( n ) nvarchar(n)
national char varying( n ) nvarchar(n)
national text ntext
timestamp rowversion
Data type synonyms can be used instead of the corresponding base data type name in data definition language
(DDL ) statements, such as CREATE TABLE, CREATE PROCEDURE, or DECL ARE @variable. However, after the
object is created, the synonyms have no visibility. When the object is created, the object is assigned the base data
type that is associated with the synonym. There is no record that the synonym was specified in the statement that
created the object.
All objects that are derived from the original object, such as result set columns or expressions, are assigned the
base data type. All subsequent metadata functions performed on the original object and any derived objects will
report the base data type, not the synonym. This behavior occurs with metadata operations, such as sp_help and
other system stored procedures, the information schema views, or the various data access API metadata
operations that report the data types of table or result set columns.
For example, you can create a table by specifying national character varying :
CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, VarCharCol national character varying(10))
VarCharCol is actually assigned an nvarchar(10) data type, and all subsequent metadata functions will report the
column as an nvarchar(10) column. The metadata functions will never report them as a national character
varying(10) column.
See also
Precision, scale, and Length (Transact-SQL)
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a
number. For example, the number 123.45 has a precision of 5 and a scale of 2.
In SQL Server, the default maximum precision of numeric and decimal data types is 38. In earlier versions of
SQL Server, the default maximum is 28.
Length for a numeric data type is the number of bytes that are used to store the number. Length for a character
string or Unicode data type is the number of characters. The length for binary, varbinary, and image data types is
the number of bytes. For example, an int data type can hold 10 digits, is stored in 4 bytes, and does not accept
decimal points. The int data type has a precision of 10, a length of 4, and a scale of 0.
When two char, varchar, binary, or varbinary expressions are concatenated, the length of the resulting
expression is the sum of the lengths of the two source expressions or 8,000 characters, whichever is less.
When two nchar or nvarchar expressions are concatenated, the length of the resulting expression is the sum of
the lengths of the two source expressions or 4,000 characters, whichever is less.
When two expressions of the same data type but different lengths are compared by using UNION, EXCEPT, or
INTERSECT, the resulting length is the maximum length of the two expressions.
The precision and scale of the numeric data types besides decimal are fixed. If an arithmetic operator has two
expressions of the same type, the result has the same data type with the precision and scale defined for that type. If
an operator has two expressions with different numeric data types, the rules of data type precedence define the
data type of the result. The result has the precision and scale defined for its data type.
The following table defines how the precision and scale of the result are calculated when the result of an operation
is of type decimal. The result is decimal when either of the following is true:
Both expressions are decimal.
One expression is decimal and the other is a data type with a lower precedence than decimal.
The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with
precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale
defined for the data type of the expression.
OPERATION RESULT PRECISION RESULT SCALE *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)

OPERATION RESULT PRECISION RESULT SCALE *
e1 % e2 min(p1-s1, p2 -s2) + max( s1,s2 ) max(s1, s2)
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it is
reduced to 38, and the corresponding scale is reduced to try to prevent the integral part of a result from being
truncated. In some cases such as multiplication or division, scale factor will not be reduced in order to keep decimal
precision, although the overflow error can be raised.
In addition and subtraction operations we need max(p1 – s1, p2 – s2) places to store integral part of the decimal
number. If there is not enough space to store them i.e. max(p1 – s1, p2 – s2) < min(38, precision) – scale , the
scale is reduced to provide enough space for integral part. Resulting scale is
MIN(precision, 38) - max(p1 – s1, p2 – s2) , so the fractional part might be rounded to fit into the resulting scale.
In multiplication and division operations we need precision - scale places to store the integral part of the result.
The scale might be reduced using the following rules:
1. The resulting scale is reduced to min(scale, 38 – (precision-scale)) if the integral part is less than 32, because
it cannot be greater than 38 – (precision-scale) . Result might be rounded in this case.
2. The scale will not be changed if it is less than 6 and if the integral part is greater than 32. In this case, overflow
error might be raised if it cannot fit into decimal(38, scale)
3. The scale will be set to 6 if it is greater than 6 and if the integral part is greater than 32. In this case, both
integral part and scale would be reduced and resulting type is decimal(38,6). Result might be rounded to 6
decimal places or overflow error will be thrown if integral part cannot fit into 32 digits.
Examples
The following expression returns result 0.00000090000000000 without rounding, because result can fit into
decimal(38,17) :
select cast(0.0000009000 as decimal(30,20)) * cast(1.0000000000 as decimal(30,20)) [decimal 38,17]
In this case precision is 61, and scale is 40. Integral part (precision-scale = 21) is less than 32, so this is case (1) in
multiplication rules and scale is calculated as min(scale, 38 – (precision-scale)) = min(40, 38 – (61-40)) = 17 .
Result type is decimal(38,17) .
The following expression returns result 0.000001 to fit into decimal(38,6) :
select cast(0.0000009000 as decimal(30,10)) * cast(1.0000000000 as decimal(30,10)) [decimal(38, 6)]
In this case precision is 61, and scale is 20. Scale is greater than 6 and integral part ( precision-scale = 41 ) is
greater than 32. This is case (3) in multiplication rules and result type is decimal(38,6) .
See also
bit (Transact-SQL)
An integer data type that can take a value of 1, 0, or NULL.
Remarks
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table,
the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and
so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is
converted to 0.
Converting to bit promotes any nonzero value to 1.
See also
ALTER TABLE (Transact-SQL )
Data Type Conversion (Database Engine)
DECL ARE @local_variable (Transact-SQL )
SET @local_variable (Transact-SQL )
sys.types (Transact-SQL )
cursor (Transact-SQL)
A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor.
Remarks
The operations that can reference variables and parameters having a cursor data type are:
The DECL ARE @local_variable and SET @local_variable statements.
The OPEN, FETCH, CLOSE, and DEALLOCATE cursor statements.
Stored procedure output parameters.
The CURSOR_STATUS function.
The sp_cursor_list, sp_describe_cursor, sp_describe_cursor_tables, and sp_describe_cursor_columns
system stored procedures.
The cursor_name output column of sp_cursor_list and sp_describe_cursor returns the name of the cursor
variable.
Any variables created with the cursor data type are nullable.
The cursor data type cannot be used for a column in a CREATE TABLE statement.
See also
CURSOR_STATUS (Transact-SQL )
DECL ARE CURSOR (Transact-SQL )
Date and time types
SQL Server supports the following date and time types.
In this section
date (Transact-SQL )
datetime (Transact-SQL )
datetime2 (Transact-SQL )
datetimeoffset (Transact-SQL )
smalldatetime (Transact-SQL )
time (Transact-SQL )
See also
Date and Time Data Types and Functions (Transact-SQL )
AT TIME ZONE (Transact-SQL )
date (Transact-SQL)
Defines a date in SQL Server.
date description
PROPERTY VALUE
Syntax date
Usage DECLARE @MyDate date
CREATE TABLE Table1 ( Column1 date )
Default string literal format YYYY-MM-DD
(used for down-level client) For more information, see the "Backward Compatibility for
Down-level Clients" section that follows.
Range 0001-01-01 through 9999-12-31 (1582-10-15 through

9999-12-31 for Informatica)
January 1, 1 CE through December 31, 9999 CE (October 15,

1582 CE through December 31, 9999 CE for Informatica)
Element ranges YYYY is four digits from 0001 to 9999 that represent a year.
For Informatica, YYYY is limited to the range 1582 to 9999.
MM is two digits from 01 to 12 that represent a month in the

specified year.
DD is two digits from 01 to 31, depending on the month, that

represent a day of the specified month.
Character length 10 positions
Precision, scale 10, 0
Storage size 3 bytes, fixed
Storage structure 1, 3-byte integer stores date.
Accuracy One day
Default value 1900-01-01
This value is used for the appended date part for implicit
conversion from time to datetime2 or datetimeoffset.
PROPERTY VALUE
Calendar Gregorian
User-defined fractional second precision No
Time zone offset aware and preservation No
Daylight saving aware No
Supported string literal formats for date

The following tables show the valid string literal formats for the date data type.
NUMERIC DESCRIPTION
mdy [m]m, dd, and [yy]yy represents month, day, and year in a
string with slash marks (/), hyphens (-), or periods (.) as
[m]m/dd/[yy]yy separators.
[m]m-dd-[yy]yy Only four- or two-digit years are supported. Use four-digit

years whenever possible. To specify an integer from 0001 to
[m]m.dd.[yy]yy 9999 that represents the cutoff year for interpreting two-digit
years as four-digit years, use the Configure the two digit year
myd cutoff Server Configuration Option.
mm/[yy]yy/dd Note! For Informatica, YYYY is limited to the range 1582 to

9999.
mm-[yy]yy/dd
A two-digit year that is less than or equal to the last two
[m]m.[yy]yy.dd digits of the cutoff year is in the same century as the cutoff
year. A two-digit year that is greater than the last two digits
dmy of the cutoff year is in the century that comes before the
cutoff year. For example, if the two-digit year cutoff is the
dd/[m]m/[yy]yy default 2049, the two-digit year 49 is interpreted as 2049 and
the two-digit year 50 is interpreted as 1950.
dd-[m]m-[yy]yy
The default date format is determined by the current
dd.[m]m.[yy]yy language setting. You can change the date format by using
the SET LANGUAGE and SET DATEFORMAT statements.
dym
The ydm format is not supported for date.
dd/[yy]yy/[m]m
dd-[yy]yy-[m]m
dd.[yy]yy.[m]m
ymd
[yy]yy/[m]m/dd
[yy]yy-[m]m-dd
[yy]yy-[m]m-dd
ALPHABETICAL DESCRIPTION
mon [dd][,] yyyy mon represents the full month name or the month
abbreviation given in the current language. Commas are
mon dd[,] [yy]yy optional and capitalization is ignored.
mon yyyy [dd] To avoid ambiguity, use four-digit years.
[dd] mon[,] yyyy If the day is missing, the first day of the month is supplied.
dd mon[,][yy]yy
dd [yy]yy mon
[dd] yyyy mon
yyyy mon [dd]
yyyy [dd] mon
ISO 8601 DESCRIPTON
YYYY-MM-DD Same as the SQL standard. This is the only format that is
defined as an international standard.
YYYYMMDD
UNSEPARATED DESCRIPTION
[yy]yymmdd The date data can be specified with four, six, or eight digits. A
six- or eight-digit string is always interpreted as ymd. The
yyyy[mm][dd] month and day must always be two digits. A four-digit string
is interpreted as year.
ODBC DESCRIPTION
{ d 'yyyy-mm-dd' } ODBC API specific.
W3C XML FORMAT DESCRIPTION
yyyy-mm-ddTZD Specifically supported for XML/SOAP usage.
TZD is the time zone designator (Z or +hh:mm or -hh:mm):
- hh:mm represents the time zone offset. hh is two digits,

ranging from 0 to 14, that represent the number of hours in
the time zone offset.
- MM is two digits, ranging from 0 to 59, that represent the
number of additional minutes in the time zone offset.
- + (plus) or – (minus) the mandatory sign of the time zone
offset. This indicates that the time zone offset is added or
subtracted from the Coordinated Universal Times (UTC) time
to obtain the local time. The valid range of time zone offset is
from -14:00 to +14:00.
ANSI and ISO 8601 compliance

date complies with the ANSI SQL standard definition for the Gregorian calendar: "NOTE 85 - Datetime data
types will allow dates in the Gregorian format to be stored in the date range 0001–01–01 CE through 9999–12–
31 CE."
The default string literal format, which is used for down-level clients, complies with the SQL standard form which
is defined as YYYY -MM -DD. This format is the same as the ISO 8601 definition for DATE.
NOTE
For Informatica, the range is limited to 1582-10-15 (October 15, 1582 CE) to 9999-12-31 (December 31, 9999 CE).
Backward compatibility for down-level clients

Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. The
following table shows the type mapping between an up-level instance of SQL Server and down-level clients.
DEFAULT STRING
LITERAL FORMAT
PASSED TO
SQL SERVER DATA DOWN-LEVEL DOWN-LEVEL DOWN-LEVEL DOWN-LEVEL
TYPE CLIENT ODBC OLEDB DOWN-LEVEL JDBC SQLCLIENT
time hh:mm:ss[.nnnnn SQL_WVARCHAR DBTYPE_WSTRor Java.sql.String String or SqString

nn] or DBTYPE_STR
SQL_VARCHAR
date YYYY-MM-DD SQL_WVARCHAR DBTYPE_WSTRor Java.sql.String String or SqString

or DBTYPE_STR
SQL_VARCHAR
datetime2 YYYY-MM-DD SQL_WVARCHAR DBTYPE_WSTRor Java.sql.String String or SqString

hh:mm:ss[.nnnnn or DBTYPE_STR
nn] SQL_VARCHAR
datetimeoffset YYYY-MM-DD SQL_WVARCHAR DBTYPE_WSTRor Java.sql.String String or SqString

nn] [+|-]hh:mm SQL_VARCHAR
Converting date and time data

When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or times.
For information about using the CAST and CONVERT functions with date and time data, see CAST and
CONVERT (Transact-SQL ).
Converting date to other date and time types
This section describes what occurs when a date data type is converted to other date and time data types.
When the conversion is to time(n), the conversion fails, and error message 206 is raised: "Operand type clash:
date is incompatible with time".
If the conversion is to datetime, date is copied. The following code shows the results of converting a date value
to a datetime value.
DECLARE @date date= '12-10-25';
DECLARE @datetime datetime= @date;
SELECT @date AS '@date', @datetime AS '@datetime';
--Result
--@date @datetime
------------ -----------------------
--2025-12-10 2025-12-10 00:00:00.000
--
--(1 row(s) affected)
When the conversion is to smalldatetime, the date value is in the range of a smalldatetime, the date component
is copied and the time component is set to 00:00:00.000. When the date value is outside the range of a
smalldatetime value, error message 242 is raised: "The conversion of a date data type to a smalldatetime data
type resulted in an out-of-range value.";and the smalldatetime value is set to NULL. The following code shows
the results of converting a date value to a smalldatetime value.
DECLARE @date date= '1912-10-25';

DECLARE @smalldatetime smalldatetime = @date;
SELECT @date AS '@date', @smalldatetime AS '@smalldatetime';
--Result
--@date @smalldatetime
------------ -----------------------
--1912-10-25 1912-10-25 00:00:00
--
For conversion to datetimeoffset(n), date is copied, and the time is set to 00:00.0000000 +00:00. The following
code shows the results of converting a date value to a datetimeoffset(3) value.
DECLARE @date date = '1912-10-25';

DECLARE @datetimeoffset datetimeoffset(3) = @date;
SELECT @date AS '@date', @datetimeoffset AS '@datetimeoffset';
--Result
--@date @datetimeoffset
------------ ------------------------------
--1912-10-25 1912-10-25 00:00:00.000 +00:00
--
When the conversion is to datetime2(n), the date component is copied, and the time component is set to
00:00.000000. The following code shows the results of converting a date value to a datetime2(3) value.
DECLARE @date date = '1912-10-25'

DECLARE @datetime2 datetime2(3) = @date;
SELECT @date AS '@date', @datetime2 AS '@datetime2(3)';
--Result
--@date @datetime2(3)
------------ -----------------------
--1912-10-25 1912-10-25 00:00:00.000
--
Converting string literals to date
Conversions from string literals to date and time types are permitted if all parts of the strings are in valid formats.
Otherwise, a runtime error is raised. Implicit conversions or explicit conversions that do not specify a style, from
date and time types to string literals will be in the default format of the current session. The following table shows
the rules for converting a string literal to the date data type.
INPUT STRING LITERAL DATE
ODBC DATE ODBC string literals are mapped to the datetime data type.
Any assignment operation from ODBC DATETIME literals into
a date type will cause an implicit conversion between
datetime and this type as defined by the conversion rules.
ODBC TIME See previous ODBC DATE rule.
ODBC DATETIME See previous ODBC DATE rule.
DATE only Trivial
TIME only Default values are supplied.
TIMEZONE only Default values are supplied.
DATE + TIME The DATE part of the input string is used.
DATE + TIMEZONE Not allowed.
TIME + TIMEZONE Default values are supplied.
DATE + TIME + TIMEZONE The DATE part of local DATETIME will be used.
Examples
The following example compares the results of casting a string to each date and time data type.
SELECT
CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'
,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS
'smalldatetime'
,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS
'datetime2'
,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS
'datetimeoffset';
Here is the result set.
DATA TYPE OUTPUT
time 12:35:29. 1234567
date 2007-05-08
DATA TYPE OUTPUT
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:29.123
datetime2 2007-05-08 12:35:29. 1234567
datetimeoffset 2007-05-08 12:35:29.1234567 +12:15
See also
datetime (Transact-SQL)
Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
NOTE
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard.
They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides
time zone support for globally deployed applications.
datetime Description
PROPERTY VALUE
Syntax datetime
Usage DECLARE @MyDatetime datetime
CREATE TABLE Table1 ( Column1 datetime )
Default string literal formats Not applicable
(used for down-level client)
Date range January 1, 1753, through December 31, 9999
Time range 00:00:00 through 23:59:59.997
Time zone offset range None

PROPERTY VALUE
Element ranges YYYY is four digits from 1753 through 9999 that represent a
year.
MM is two digits, ranging from 01 to 12, that represent a

month in the specified year.
DD is two digits, ranging from 01 to 31 depending on the

month, that represent a day of the specified month.
hh is two digits, ranging from 00 to 23, that represent the

hour.
mm is two digits, ranging from 00 to 59, that represent the

minute.
ss is two digits, ranging from 00 to 59, that represent the

second.
n* is zero to three digits, ranging from 0 to 999, that

represent the fractional seconds.
Character length 19 positions minimum to 23 maximum
Storage size 8 bytes
Accuracy Rounded to increments of .000, .003, or .007 seconds
Default value 1900-01-01 00:00:00
Calendar Gregorian (Does not include the complete range of years.)
Supported String Literal Formats for datetime

The following tables list the supported string literal formats for datetime. Except for ODBC, datetime string
literals are in single quotation marks ('), for example, 'string_literaL'. If the environment is not us_english, the
string literals should be in the format N'string_literaL'.
NUMERIC DESCRIPTION
Date formats: You can specify date data with a numeric month specified. For
example, 5/20/97 represents the twentieth day of May 1997.
[0]4/15/[19]96 -- (mdy) When you use numeric date format, specify the month, day,
and year in a string that uses slash marks (/), hyphens (-), or
[0]4-15-[19]96 -- (mdy) periods (.) as separators. This string must appear in the
following form:
[0]4.15.[19]96 -- (mdy)
number separator number separator number [time] [time]
[0]4/[19]96/15 -- (myd)
15/[0]4/[19]96 -- (dmy)
When the language is set to us_english, the default order for
15/[19]96/[0]4 -- (dym) the date is mdy. You can change the date order by using the
SET DATEFORMAT statement.
[19]96/15/[0]4 -- (ydm)
The setting for SET DATEFORMAT determines how date values
[19]96/[0]4/15 -- (ymd) are interpreted. If the order does not match the setting, the
values are not interpreted as dates, because they are out of
Time formats: range or the values are misinterpreted. For example, 12/10/08
can be interpreted as one of six dates, depending on the
14:30 DATEFORMAT setting. A four-part year is interpreted as the
year.
14:30[:20:999]
14:30[:20.9]
4am
4 PM
ALPHABETICAL DESCRIPTION
Apr[il] [15][,] 1996 You can specify date data with a month specified as the full
month name. For example, April or the month abbreviation of
Apr[il] 15[,] [19]96 Apr specified in the current language; commas are optional
and capitalization is ignored.
Apr[il] 1996 [15]
Here are some guidelines for using alphabetical date formats:
[15] Apr[il][,] 1996
1) Enclose the date and time data in single quotation marks
15 Apr[il][,][19]96 ('). For languages other than English, use N'
15 [19]96 apr[il] 2) Characters that are enclosed in brackets are optional.
[15] 1996 apr[il] 3) If you specify only the last two digits of the year, values less
than the last two digits of the value of the Configure the two
1996 APR[IL] [15] digit year cutoff Server Configuration Option configuration
option are in the same century as the cutoff year. Values
1996 [15] APR[IL] greater than or equal to the value of this option are in the
century that comes before the cutoff year. For example, if two
digit year cutoff is 2050 (default), 25 is interpreted as 2025
and 50 is interpreted as 1950. To avoid ambiguity, use four-
digit years.
4) If the day is missing, the first day of the month is supplied.
The SET DATEFORMAT session setting is not applied when you

specify the month in alphabetical form.
ISO 8601 DESCRIPTION
YYYY-MM-DDThh:mm:ss[.mmm] Examples:
YYYYMMDD[ hh:mm:ss[.mmm]] 1) 2004-05-23T14:25:10
2) 2004-05-23T14:25:10.487
To use the ISO 8601 format, you must specify each element in
the format. This also includes the T, the colons (:), and the
period (.) that are shown in the format.
The brackets indicate that the fraction of second component is

optional. The time component is specified in the 24-hour
format.
The T indicates the start of the time part of the datetime

value.
The advantage in using the ISO 8601 format is that it is an

international standard with unambiguous specification. Also,
this format is not affected by the SET DATEFORMAT or SET
LANGUAGE setting.
UNSEPARATED DESCRIPTION
YYYYMMDD hh:mm:ss[.mmm]
ODBC DESCRIPTION
{ ts '1998-05-02 01:23:56.123' } The ODBC API defines escape sequences to represent date
and time values, which ODBC calls timestamp data. This
{ d '1990-10-02' } ODBC timestamp format is also supported by the OLE DB
language definition (DBGUID-SQL) supported by the
{ t '13:33:41' } Microsoft OLE DB provider for SQL Server. Applications that
use the ADO, OLE DB, and ODBC-based APIs can use this
ODBC timestamp format to represent dates and times.
ODBC timestamp escape sequences are of the format: {

literal_type 'constant_value' }:
- literal_type specifies the type of the escape sequence.

Timestamps have three literal_type specifiers:
1) d = date only
2) t = time only
3) ts = timestamp (time + date)
- 'constant_value' is the value of the escape sequence.

constant_value must follow these formats for each
literal_type.
d : yyyy-mm-dd
t : hh:mm:ss[.fff ]
ts : yyyy-mm-dd hh:mm:ss[.fff ]
Rounding of datetime Fractional Second Precision
datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.
USER-SPECIFIED VALUE SYSTEM STORED VALUE
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995 1998-01-01 23:59:59.997
01/01/98 23:59:59.996
01/01/98 23:59:59.997
01/01/98 23:59:59.998
01/01/98 23:59:59.992 1998-01-01 23:59:59.993
01/01/98 23:59:59.993
01/01/98 23:59:59.994
01/01/98 23:59:59.990 1998-01-01 23:59:59.990
01/01/98 23:59:59.991
ANSI and ISO 8601 Compliance

datetime is not ANSI or ISO 8601 compliant.
Converting Date and Time Data

Converting Other Date and Time Types to the datetime Data Type
This section describes what occurs when other date and time data types are converted to the datetime data type.
When the conversion is from date, the year, month and day are copied. The time component is set to 00:00:00.000.
The following code shows the results of converting a date value to a datetime value.

DECLARE @datetime datetime = @date;
SELECT @datetime AS '@datetime', @date AS '@date';
--Result
--@datetime @date
------------------------- ----------
--2016-12-21 00:00:00.000 2016-12-21
When the conversion is from time(n), the time component is copied, and the date component is set to '1900-01-
01'. When the fractional precision of the time(n) value is greater than three digits, the value will be truncated to fit.
The following example shows the results of converting a time(4) value to a datetime value.
DECLARE @time time(4) = '12:10:05.1237';
DECLARE @datetime datetime = @time;
SELECT @datetime AS '@datetime', @time AS '@time';
--Result
--@datetime @time
------------------------- -------------
--1900-01-01 12:10:05.123 12:10:05.1237
When the conversion is from smalldatetime, the hours and minutes are copied. The seconds and fractional
seconds are set to 0. The following code shows the results of converting a smalldatetime value to a datetime
value.
DECLARE @smalldatetime smalldatetime = '12-01-16 12:32';

DECLARE @datetime datetime = @smalldatetime;
SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime';
--Result
--@datetime @smalldatetime
------------------------- -----------------------
--2016-12-01 12:32:00.000 2016-12-01 12:32:00
When the conversion is from datetimeoffset(n), the date and time components are copied. The time zone is
truncated. When the fractional precision of the datetimeoffset(n) value is greater than three digits, the value will
be truncated. The following example shows the results of converting a datetimeoffset(4) value to a datetime
value.
DECLARE @datetimeoffset datetimeoffset(4) = '1968-10-23 12:45:37.1234 +10:0';

DECLARE @datetime datetime = @datetimeoffset;
SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset';
--Result
--@datetime @datetimeoffset
------------------------- ------------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237 +01:0
When the conversion is from datetime2(n), the date and time are copied. When the fractional precision of the
datetime2(n) value is greater than three digits, the value will be truncated. The following example shows the
results of converting a datetime2(4) value to a datetime value.
DECLARE @datetime2 datetime2(4) = '1968-10-23 12:45:37.1237';

DECLARE @datetime datetime = @datetime2;
SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2';
--Result
--@datetime @datetime2
------------------------- ------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237
Examples
SELECT
'smalldatetime'
'datetime2'
'datetimeoffset';
DATA TYPE OUTPUT
time 12:35:29. 1234567
date 2007-05-08
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:29.123
datetime2 2007-05-08 12:35:29. 1234567
datetimeoffset 2007-05-08 12:35:29.1234567 +12:15
See also
datetime2 (Transact-SQL)
Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as
an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and
optional user-specified precision.
datetime2 description
PROPERTY VALUE
Syntax datetime2 [ (fractional seconds precision) ]
Usage DECLARE @MyDatetime2 datetime2(7)
CREATE TABLE Table1 ( Column1 datetime2(7) )
Default string literal format YYYY-MM-DD hh:mm:ss[.fractional seconds]
Date range 0001-01-01 through 9999-12-31
January 1,1 CE through December 31, 9999 CE
Time range 00:00:00 through 23:59:59.9999999
Time zone offset range None
Element ranges YYYY is a four-digit number, ranging from 0001 through

9999, that represents a year.
MM is a two-digit number, ranging from 01 to 12, that

represents a month in the specified year.
DD is a two-digit number, ranging from 01 to 31 depending

on the month, that represents a day of the specified month.
hh is a two-digit number, ranging from 00 to 23, that

represents the hour.
mm is a two-digit number, ranging from 00 to 59, that

represents the minute.
ss is a two-digit number, ranging from 00 to 59, that

represents the second.
n* is a zero- to seven-digit number from 0 to 9999999 that

represents the fractional seconds. In Informatica, the fractional
seconds will be truncated when n > 3.
PROPERTY VALUE
Character length 19 positions minimum (YYYY-MM-DD hh:mm:ss ) to 27

maximum (YYYY-MM-DD hh:mm:ss.0000000)
Precision, scale 0 to 7 digits, with an accuracy of 100ns. The default precision

is 7 digits.
Storage size 6 bytes for precisions less than 3; 7 bytes for precisions 3 and
4. All other precisions require 8 bytes.
Accuracy 100 nanoseconds
Default value 1900-01-01 00:00:00
Calendar Gregorian
User-defined fractional second precision Yes
For data type metadata, see sys.systypes (Transact-SQL ) or TYPEPROPERTY (Transact-SQL ). Precision and scale
are variable for some date and time data types. To obtain the precision and scale for a column, see
COLUMNPROPERTY (Transact-SQL ), COL_LENGTH (Transact-SQL ), or sys.columns (Transact-SQL ).
Supported string literal formats for datetime2

The following tables list the supported ISO 8601 and ODBC string literal formats for datetime2. For information
about alphabetical, numeric, unseparated, and time formats for the date and time parts of datetime2, see date
(Transact-SQL ) and time (Transact-SQL ).
ISO 8601 DESCRIPTIONS
YYYY-MM-DDThh:mm:ss[.nnnnnnn] This format is not affected by the SET LANGUAGE and SET
DATEFORMAT session locale settings. The T, the colons (:) and
YYYY-MM-DDThh:mm:ss[.nnnnnnn] the period (.) are included in the string literal, for example
'2007-05-02T19:58:47.1234567'.
ODBC DESCRIPTION
{ ts 'yyyy-mm-dd hh:mm:ss[.fractional seconds]' } ODBC API specific:
The number of digits to the right of the decimal point, which

represents the fractional seconds, can be specified from 0 up
to 7 (100 nanoseconds).

The ANSI and ISO 8601 compliance of date and time apply to datetime2.
Backward Compatibility for Down-level Clients

Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. The following
table shows the type mapping between an up-level instance of SQL Server and down-level clients.
DEFAULT STRING
LITERAL FORMAT
PASSED TO

nn] or DBTYPE_STR
SQL_VARCHAR

or DBTYPE_STR
SQL_VARCHAR

nn] SQL_VARCHAR


CONVERT (Transact-SQL )
Converting other date and time types to the datetime2 data type
This section describes what occurs when other date and time data types are converted to the datetime2 data type.
When the conversion is from date, the year, month and day are copied. The time component is set to
00:00:00.0000000. The following code shows the results of converting a date value to a datetime2 value.

DECLARE @datetime2 datetime2 = @date;
SELECT @datetime2 AS '@datetime2', @date AS '@date';
--Result
--@datetime2 @date
----------------------------- ----------
--2016-12-21 00:00:00.0000000 2016-12-21
When the conversion is from time(n), the time component is copied, and the date component is set to '1900-01-
01'. The following example shows the results of converting a time(7) value to a datetime2 value.
DECLARE @datetime2 datetime2 = @time;
SELECT @datetime2 AS '@datetime2', @time AS '@time';
--Result
--@datetime2 @time
----------------------------- ----------------
--1900-01-01 12:10:16.1234567 12:10:16.1234567
When the conversion is from smalldatetime, the hours and minutes are copied. The seconds and fractional
seconds are set to 0. The following code shows the results of converting a smalldatetime value to a datetime2
value.
DECLARE @smalldatetime smalldatetime = '12-01-16 12:32';

DECLARE @datetime2 datetime2 = @smalldatetime;
SELECT @datetime2 AS '@datetime2', @smalldatetime AS '@smalldatetime';
--Result
--@datetime2 @smalldatetime
----------------------------- -----------------------
--2016-12-01 12:32:00.0000000 2016-12-01 12:32:00
When the conversion is from datetimeoffset(n), the date and time components are copied. The time zone is
truncated. The following example shows the results of converting a datetimeoffset(7) value to a datetime2 value.

DECLARE @datetime2 datetime2 = @datetimeoffset;
SELECT @datetime2 AS '@datetime2', @datetimeoffset AS '@datetimeoffset';
--Result
--@datetime2 @datetimeoffset
----------------------------- ----------------------------------
--2016-10-23 12:45:37.1234567 2016-10-23 12:45:37.1234567 +10:00
When the conversion is from datetime, the date and time are copied. The fractional precision is extended to 7
digits. The following example shows the results of converting a datetime value to a datetime2 value.
DECLARE @datetime datetime = '2016-10-23 12:45:37.333';

DECLARE @datetime2 datetime2 = @datetime;
SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime';
--Result
--@datetime2 @datetime
------------------------- ---------------------------
--2016-10-23 12:45:37.3333333 2016-10-23 12:45:37.333
Converting String Literals to datetime2

the rules for converting a string literal to the datetime2 data type.
INPUT STRING LITERAL DATETIME2(N)
datetime2 types will cause an implicit conversion between
DATE only The TIME part defaults to 00:00:00.
TIME only The DATE part defaults to 1900-1-1.
DATE + TIME Trivial
TIME + TIMEZONE The DATE part defaults to 1900-1-1. TIMEZONE input is

ignored.
DATE + TIME + TIMEZONE The local DATETIME will be used.
Examples
SELECT
'smalldatetime'
'datetime2'
'datetimeoffset';
DATA TYPE OUTPUT
time 12:35:29. 1234567
date 2007-05-08
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:29.123
datetime2 2007-05-08 12:35:29. 1234567

DATA TYPE OUTPUT
datetimeoffset 2007-05-08 12:35:29.1234567 +12:15
See also
datetimeoffset (Transact-SQL)
Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour
clock.
datetimeoffset description
PROPERTY VALUE
Syntax datetimeoffset [ (fractional seconds precision) ]
Usage DECLARE @MyDatetimeoffset datetimeoffset(7)
CREATE TABLE Table1 ( Column1 datetimeoffset(7) )
Default string literal formats (used for down-level client) YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]
For more information, see the "Backward Compatibility for

January 1, 1 CE through December 31, 9999 CE
Time range 00:00:00 through 23:59:59.9999999 (fractional seconds are

not supported in Informatica)
Time zone offset range -14:00 through +14:00 (the time zone offset is ignored in
Informatica)
PROPERTY VALUE
Element ranges YYYY is four digits, ranging from 0001 through 9999, that
represent a year.



hour.

minute.

second.
n* is zero to seven digits, ranging from 0 to 9999999, that

represent the fractional seconds. Fractional seconds are not
supported in Informatica.
hh is two digits that range from -14 to +14. The time zone
offset is ignored in Informatica.
mm is two digits that range from 00 to 59. The time zone

offset is ignored in Informatica.
Character length 26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm)

to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-
}hh:mm)
Precision, scale See the table below.
Storage size 10 bytes, fixed is the default with the default of 100ns
fractional second precision.
Accuracy 100 nanoseconds
Default value 1900-01-01 00:00:00 00:00
Calendar Gregorian
Time zone offset aware and preservation Yes
FRACTIONAL SECONDS
SPECIFIED SCALE RESULT (PRECISION, SCALE) COLUMN LENGTH (BYTES) PRECISION
datetimeoffset (34,7) 10 7
datetimeoffset(0) (26,0) 8 0-2

FRACTIONAL SECONDS
Supported string literal formats for datetimeoffset

The following table lists the supported ISO 8601 string literal formats for datetimeoffset. For information about
alphabetical, numeric, unseparated and time formats for the date and time parts of datetimeoffset, see date
(Transact-SQL ) and time (Transact-SQL ).
ISO 8601 DESCRIPTION
YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm] These two formats are not affected by the SET LANGUAGE
and SET DATEFORMAT session locale settings. Spaces are not
allowed between the datetimeoffset and the datetime
parts.
YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC) This format by ISO definition indicates the datetime portion
should be expressed in Coordinated Universal Time (UTC). For
example, 1999-12-12 12:30:30.12345 -07:00 should be
represented as 1999-12-12 19:30:30.12345Z.
Time zone offset

A time zone offset specifies the zone offset from UTC for a time or datetime value. The time zone offset can be
represented as [+|-] hh:mm:
hh is two digits that range from 00 to 14 and represent the number of hours in the time zone offset.
mm is two digits, ranging from 00 to 59, that represent the number of additional minutes in the time zone
offset.
+ (plus) or – (minus) is the mandatory sign for a time zone offset. This indicates whether the time zone offset is
added or subtracted from the UTC time to obtain the local time. The valid range of time zone offset is from -
14:00 to +14:00.
The time zone offset range follows the W3C XML standard for XSD schema definition and is slightly different
from the SQL 2003 standard definition, 12:59 to +14:00.
The optional type parameter fractional seconds precision specifies the number of digits for the fractional part of
the seconds. This value can be an integer with 0 to 7 (100 nanoseconds). The default fractional seconds precision is
100ns (seven digits for the fractional part of the seconds).
The data is stored in the database and processed, compared, sorted, and indexed in the server as in UTC. The time
zone offset will be preserved in the database for retrieval.
The given time zone offset will be assumed to be daylight saving time (DST) aware and adjusted for any given
datetime that is in the DST period.
For datetimeoffset type, both UTC and local (to the persistent or converted time zone offset) datetime value will
be validated during insert, update, arithmetic, convert, or assign operations. The detection of any invalid UTC or
local (to the persistent or converted time zone offset) datetime value will raise an invalid value error. For example,
9999-12-31 10:10:00 is valid in UTC, but overflow in local time to the time zone offset +13:50.
To convert a date to a corresponding datetimeoffset value in a target time zone, see AT TIME ZONE (Transact-
SQL ).

The ANSI and ISO 8601 Compliance sections of the date and time topics apply to datetimeoffset.
Backward compatibility for down-level clients

Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. The following
table shows the type mapping between an up-level instance of SQL Server and down-level clients.
DEFAULT STRING
LITERAL FORMAT
PASSED TO

nn] or DBTYPE_STR
SQL_VARCHAR

or DBTYPE_STR
SQL_VARCHAR

nn] SQL_VARCHAR


Converting datetimeoffset data type to other date and time types
This section describes what occurs when a datetimeoffset data type is converted to other date and time data
types.
When converting to date, the year, month, and day are copied. The following code shows the results of converting
a datetimeoffset(4) value to a date value.
DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';
DECLARE @date date= @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';
--Result
--@datetimeoffset date
-------------------------------- ----------
--2025-12-10 12:32:10.0000 +01:0 2025-12-10
--
If the conversion is to time(n), the hour, minute, second, and fractional seconds are copied. The time zone value is
truncated. When the precision of the datetimeoffset(n) value is greater than the precision of the time(n) value,
the value is rounded up. The following code shows the results of converting a datetimeoffset(4) value to a
time(3) value.

DECLARE @time time(3) = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time';
--Result
--@datetimeoffset time
-------------------------------- ------------
-- 2025-12-10 12:32:10.1237 +01:00 12:32:10.124
--
When converting todatetime, the date and time values are copied, and the time zone is truncated. When the
fractional precision of the datetimeoffset(n) value is greater than three digits, the value is truncated. The
following code shows the results of converting a datetimeoffset(4) value to a datetime value.

DECLARE @datetime datetime = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';
--Result
--@datetimeoffset datetime
-------------------------------- -----------------------
--2025-12-10 12:32:10.1237 +01:0 2025-12-10 12:32:10.123
--
For conversions to smalldatetime, the date and hours are copied. The minutes are rounded up with respect to the
seconds value and seconds are set to 0. The following code shows the results of converting a datetimeoffset(3)
value to a smalldatetime value.
DECLARE @datetimeoffset datetimeoffset(3) = '1912-10-25 12:24:32 +10:0';
DECLARE @smalldatetime smalldatetime = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime';
--Result
--@datetimeoffset @smalldatetime
-------------------------------- -----------------------
--1912-10-25 12:24:32.000 +10:00 1912-10-25 12:25:00
--
If the conversion is to datetime2(n), the date and time are copied to the datetime2 value, and the time zone is
truncated. When the precision of the datetime2(n) value is greater than the precision of the datetimeoffset(n)
value, the fractional seconds are truncated to fit. The following code shows the results of converting a
datetimeoffset(4) value to a datetime2(3) value.

DECLARE @datetime2 datetime2(3)=@datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';
--Result
@datetimeoffset @datetime2
---------------------------------- ----------------------
1912-10-25 12:24:32.1277 +10:00 1912-10-25 12:24:32.12
Converting string literals to datetimeoffset

the rules for converting a string literal to the datetimeoffset data type.
INPUT STRING LITERAL DATETIMEOFFSET(N)
datetimeoffset types will cause an implicit conversion
between datetime and this type as defined by the conversion
rules.
DATE only The TIME part defaults to 00:00:00. The TIMEZONE defaults
to +00:00.
TIME only The DATE part defaults to 1900-1-1. The TIMEZONE will
default to +00:00.
TIMEZONE only Default values are supplied
DATE + TIME The TIMEZONE defaults to +00:00.

INPUT STRING LITERAL DATETIMEOFFSET(N)
DATE + TIMEZONE Not allowed
TIME + TIMEZONE The DATE part defaults to 1900-1-1.
DATE + TIME + TIMEZONE Trivial
Examples
SELECT
'smalldatetime'
,CAST('2007-05-08 12:35:29.1234567+12:15' AS datetime2(7)) AS
'datetime2'
'datetimeoffset'
,CAST('2007-05-08 12:35:29.1234567+12:15' AS datetimeoffset(7)) AS
'datetimeoffset IS08601';
DATA TYPE OUTPUT
Time 12:35:29. 1234567
Date 2007-05-08
Smalldatetime 2007-05-08 12:35:00
Datetime 2007-05-08 12:35:29.123
datetime2 2007-05-08 12:35:29. 1234567
Datetimeoffset 2007-05-08 12:35:29.1234567 +12:15
See also
AT TIME ZONE (Transact-SQL )
smalldatetime (Transact-SQL)
Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero
(:00) and without fractional seconds.
NOTE
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard.
They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides
time zone support for globally deployed applications.
smalldatetime description
Syntax smalldatetime
Usage DECLARE @MySmalldatetime smalldatetime
CREATE TABLE Table1 ( Column1 smalldatetime )
Default string literal formats Not applicable
(used for down-level client)
January 1, 1900, through June 6, 2079
Time range 00:00:00 through 23:59:59
2007-05-09 23:59:59 will round to
2007-05-10 00:00:00
Element ranges YYYY is four digits, ranging from 1900, to 2079, that
represent a year.



hour.

minute.

second. Values that are 29.998 seconds or less are rounded
down to the nearest minute, Values of 29.999 seconds or
more are rounded up to the nearest minute.
Character length 19 positions maximum
Storage size 4 bytes, fixed.
Accuracy One minute
Default value 1900-01-01 00:00:00
Calendar Gregorian
(Does not include the complete range of years.)
ANSI and ISO 8601 Compliance

smalldatetime is not ANSI or ISO 8601 compliant.

Converting smalldatetime to other date and time types
This section describes what occurs when a smalldatetime data type is converted to other date and time data
types.
In the case of conversion to date, the year, month, and day are copied. The following code shows the results of
converting a smalldatetime value to a date value.
DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';
DECLARE @date date = @smalldatetime
SELECT @smalldatetime AS '@smalldatetime', @date AS 'date';
--Result
--@smalldatetime date
------------------------- ----------
--1955-12-13 12:43:00 1955-12-13
--
When the conversion is to time(n), the hours, minutes, and seconds are copied. The fractional seconds are set to
0. The following code shows the results of converting a smalldatetime value to a time(4) value.

DECLARE @time time(4) = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @time AS 'time';
--Result
--@smalldatetime time
------------------------- -------------
--1955-12-13 12:43:00 12:43:00.0000
--
When the conversion is to datetime, the smalldatetime value is copied to the datetime value. The fractional
seconds are set to 0. The following code shows the results of converting a smalldatetime value to a datetime
value.

DECLARE @datetime datetime = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @datetime AS 'datetime';
--Result
--@smalldatetime datetime
------------------------- -----------------------
--1955-12-13 12:43:00 1955-12-13 12:43:00.000
--
In the case of conversion to datetimeoffset(n), the smalldatetime value is copied to the datetimeoffset(n)
value. The fractional seconds are set to 0, and the time zone offset is set to +00:0. The following code shows the
results of converting a smalldatetime value to a datetimeoffset(4) value.

DECLARE @datetimeoffset datetimeoffset(4) = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @datetimeoffset AS 'datetimeoffset(4)';
--Result
--@smalldatetime datetimeoffset(4)
------------------------- ------------------------------
--1955-12-13 12:43:00 1955-12-13 12:43:00.0000 +00:0
--
For the conversion to datetime2(n), the smalldatetime value is copied to the datetime2(n) value. The
fractional seconds are set to 0. The following code shows the results of converting a smalldatetime value to a
datetime2(4) value.

DECLARE @datetime2 datetime2(4) = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @datetime2 AS ' datetime2(4)';
--Result
--@smalldatetime datetime2(4)
------------------------- ------------------------
--1955-12-13 12:43:00 1955-12-13 12:43:00.0000
--
Examples
A. Casting string literals with seconds to smalldatetime
The following example compares the conversion of seconds in string literals to smalldatetime .
SELECT
CAST('2007-05-08 12:35:29' AS smalldatetime)
,CAST('2007-05-08 12:35:30' AS smalldatetime)
,CAST('2007-05-08 12:59:59.998' AS smalldatetime);
INPUT OUTPUT
2007-05-08 12:35:29 2007-05-08 12:35:00
2007-05-08 12:35:30 2007-05-08 12:36:00
2007-05-08 12:59:59.998 2007-05-08 13:00:00
B. Comparing date and time data types

SELECT
'smalldatetime'
'datetime2'
'datetimeoffset';
DATA TYPE OUTPUT
time 12:35:29. 1234567
date 2007-05-08
DATA TYPE OUTPUT
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:29.123
datetime2 2007-05-08 12:35:29. 1234567
datetimeoffset 2007-05-08 12:35:29.1234567 +12:15
See also
time (Transact-SQL)
Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.
NOTE
Informatica information is provided for PDW customers using the Informatica Connector.
time Description
PROPERTY VALUE
Syntax time [ (fractional second scale) ]
Usage DECLARE @MyTime time(7)
CREATE TABLE Table1 ( Column1 time(7) )
fractional seconds scale Specifies the number of digits for the fractional part of the
seconds.
This can be an integer from 0 to 7. For Informatica, this can

be an integer from 0 to 3.
The default fractional scale is 7 (100ns).
Default string literal format hh:mm:ss[.nnnnnnn] for Informatica)
Down-level Clients" section that follows..
Range 00:00:00.0000000 through 23:59:59.9999999 (00:00:00.000

through 23:59:59.999 for Informatica)
Element ranges hh is two digits, ranging from 0 to 23, that represent the
hour.

minute.

second.
n* is zero to seven digits, ranging from 0 to 9999999, that

represent the fractional seconds. For Informatica, n* is zero to
three digits, ranging from 0 to 999.
PROPERTY VALUE
Character length 8 positions minimum (hh:mm:ss) to 16 maximum

(hh:mm:ss.nnnnnnn). For Informatica, the maximum is 12
(hh:mm:ss.nnn).
Precision, scale See the table below.
(user specifies scale only)
Storage size 5 bytes, fixed, is the default with the default of 100ns
fractional second precision. In Informatica, the default is 4
bytes, fixed, with the default of 1ms fractional second
precision.
Accuracy 100 nanoseconds (1 millisecond in Informatica)
Default value 00:00:00
This value is used for the appended time part for implicit
conversion from date to datetime2 or datetimeoffset.
FRACTIONAL
SECONDS
time (16,7) [(12,3) in Informatica] 5 (4 in Informatica) 7 (3 in Informatica)
time(0) (8,0) 3 0-2
time(1) (10,1) 3 0-2
time(2) (11,2) 3 0-2
time(3) (12,3) 4 3-4
time(4) (13,4) 4 3-4
Not supported in
Informatica.
time(5) (14,5) 5 5-7
Not supported in
Informatica.
FRACTIONAL
SECONDS
time(6) (15,6) 5 5-7
Not supported in
Informatica.
time(7) (16,7) 5 5-7
Not supported in
Informatica.
Supported String Literal Formats for time

The following table shows the valid string literal formats for the time data type.
SQL SERVER DESCRIPTION
hh:mm[:ss][:fractional seconds][AM][PM] The hour value of 0 represents the hour after midnight (AM),
regardless of whether AM is specified. PM cannot be specified
hh:mm[:ss][.fractional seconds][AM][PM] when the hour equals 0.
hhAM[PM] Hour values from 01 through 11 represent the hours before

noon if neither AM nor PM is specified. The values represent
hh AM[PM] the hours before noon when AM is specified. The values
represent hours after noon if PM is specified.
The hour value 12 represents the hour that starts at noon if

neither AM nor PM is specified. If AM is specified, the value
represents the hour that starts at midnight. If PM is specified,
the value represents the hour that starts at noon. For
example, 12:01 is 1 minute after noon, as is 12:01 PM; and
12:01 AM is one minute after midnight. Specifying 12:01 AM
is the same as specifying 00:01 or 00:01 AM.
Hour values from 13 through 23 represent hours after noon

if AM or PM is not specified. The values also represent the
hours after noon when PM is specified. AM cannot be
specified when the hour value is from 13 through 23.
An hour value of 24 is not valid. To represent midnight, use

12:00 AM or 00:00.
Milliseconds can be preceded by either a colon (:) or a period

(.). If a colon is used, the number means thousandths-of-a-
second. If a period is used, a single digit means tenths-of-a-
second, two digits mean hundredths-of-a-second, and three
digits mean thousandths-of-a-second. For example,
12:30:20:1 indicates 20 and one-thousandth seconds past
12:30; 12:30:20.1 indicates 20 and one-tenth seconds past
12:30.
ISO 8601 NOTES
hh:mm:ss hh is two digits, ranging from 0 to 14, that represent the

number of hours in the time zone offset.
hh:mm[:ss][.fractional seconds]
number of additional minutes in the time zone offset.
ODBC NOTES
{t 'hh:mm:ss[.fractional seconds]'} ODBC API specific.
Compliance with ANSI and ISO 8601 Standards

Using hour 24 to represent midnight and leap second over 59 as defined by ISO 8601 (5.3.2 and 5.3) are not
supported to be backward compatible and consistent with the existing date and time types.
The default string literal format (used for down-level client) will align with the SQL standard form, which is
defined as hh:mm:ss[.nnnnnnn]. This format resembles the ISO 8601 definition for TIME excluding fractional
seconds.
Backward Compatibility for Down-level Clients

Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. The
following table shows the type mapping between an up-level instance of SQL Server and down-level clients.
DEFAULT STRING
LITERAL FORMAT
PASSED TO
SQL SERVER DATA DOWN-LEVEL DOWN-LEVEL DOWN-LEVEL DOWN-LEVEL DOWN-LEVEL
TYPE CLIENT ODBC OLEDB JDBC SQLCLIENT

nn] or DBTYPE_STR
SQL_VARCHAR

or DBTYPE_STR
SQL_VARCHAR

nn] SQL_VARCHAR

Converting Date and Time Data

When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or
times. For information about using the CAST and CONVERT functions with date and time data, see CAST and
Converting time (n) Data Type to Other Date and Time Types
This section describes what occurs when a time data type is converted to other date and time data types.
When the conversion is to time(n), the hour, minute, and seconds are copied. When the destination precision is
less than the source precision, the fractional seconds is rounded up to fit the destination precision. The following
example shows the results of converting a time(4) value to a time(3) value.
DECLARE @timeFrom time(4) = '12:34:54.1237';

DECLARE @timeTo time(3) = @timeFrom;
SELECT @timeTo AS 'time(3)', @timeFrom AS 'time(4)';
--Results
--time(3) time(4)
-------------- -------------
--12:34:54.124 12:34:54.1237
--
If the conversion is to
date, the conversion fails, and error message 206 is raised: "Operand type clash: date is incompatible with time".
When the conversion is to datetime, hour, minute, and second values are copied; and the date component is set
to '1900-01-01'. When the fractional seconds precision of the time(n) value is greater than three digits, the
datetime result will be truncated. The following code shows the results of converting a time(4) value to a
datetime value.

DECLARE @datetime datetime= @time;
SELECT @time AS '@time', @datetime AS '@datetime';
--Result
--@time @datetime
--------------- -----------------------
--12:15:04.1237 1900-01-01 12:15:04.123
--
When the conversion is to smalldatetime, the date is set to '1900-01-01', and the hour and minute values are
rounded up. The seconds and fractional seconds are set to 0. The following code shows the results of converting a
time(4) value to a smalldatetime value.
-- Shows rounding up of the minute value.

DECLARE @smalldatetime smalldatetime= @time;
SELECT @time AS '@time', @smalldatetime AS '@smalldatetime';
--Result
@time @smalldatetime
---------------- -----------------------
12:15:59.9999 1900-01-01 12:16:00--
-- Shows rounding up of the hour value.

DECLARE @smalldatetime smalldatetime= @time;
SELECT @time AS '@time', @smalldatetime AS '@smalldatetime';

@time @smalldatetime
---------------- -----------------------
12:59:59.9999 1900-01-01 13:00:00
(1 row(s) affected)
If the conversion is to datetimeoffset(n), the date is set to '1900-01-01', and the time is copied. The time zone
offset is set to +00:00. When the fractional seconds precision of the time(n) value is greater than the precision of
the datetimeoffset(n) value, the value is rounded up to fit. The following example shows the results of
converting a time(4) value to a datetimeoffset(3) type.

DECLARE @datetimeoffset datetimeoffset(3) = @time;
SELECT @time AS '@time', @datetimeoffset AS '@datetimeoffset';
--Result
--@time @datetimeoffset
--------------- ------------------------------
--12:15:04.1237 1900-01-01 12:15:04.124 +00:00
--
When converting to datetime2(n), the date is set to '1900-01-01', the time component is copied, and the time
zone offset is set to 00:00. When the fractional seconds precision of the datetime2(n) value is greater than the
time(n) value, the value will be rounded up to fit. The following example shows the results of converting a
time(4) value to a datetime2(2) value.

DECLARE @datetime2 datetime2(3) = @time;
SELECT @datetime2 AS '@datetime2', @time AS '@time';
--Result
--@datetime2 @time
------------------------- -------------
--1900-01-01 12:15:04.124 12:15:04.1237
--
Converting String Literals to time (n)

the rules for converting a string literal to the time data type.
INPUT STRING LITERAL CONVERSION RULE
timetypes will cause an implicit conversion between
ODBC TIME See ODBC DATE rule above.
ODBC DATETIME See ODBC DATE rule above.
DATE only Default values are supplied.
TIME only Trivial

INPUT STRING LITERAL CONVERSION RULE
DATE + TIME The TIME part of the input string is used.
TIME + TIMEZONE The TIME part of the input string is used.
DATE + TIME + TIMEZONE The TIME part of local DATETIME will be used.
Examples
A. Comparing date and time Data Types
SELECT
'smalldatetime'
'datetime2'
'datetimeoffset';
DATA TYPE OUTPUT
time 12:35:29. 1234567
date 2007-05-08
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:29.123
datetime2 2007-05-08 12:35:29. 1234567
datetimeoffset 2007-05-08 12:35:29.1234567 +12:15
B. Inserting Valid Time String Literals into a time (7) Column

The following table lists different string literals that can be inserted into a column of data type time(7) with the
values that are then stored in that column.
STRING LITERAL FORMAT TIME(7) VALUE THAT IS

TYPE INSERTED STRING LITERAL STORED DESCRIPTION
SQL Server '01:01:01:123AM' 01:01:01.1230000 When a colon (:) comes

before fractional seconds
precision, scale cannot
exceed three positions or an
error will be raised.
STRING LITERAL FORMAT TIME(7) VALUE THAT IS
TYPE INSERTED STRING LITERAL STORED DESCRIPTION
SQL Server '01:01:01.1234567 AM' 01:01:01.1234567 When AM or PM is

specified, the time is stored
in 24-hour format without
the literal AM or PM
SQL Server '01:01:01.1234567 PM' 13:01:01.1234567 When AM or PM is

specified, the time is stored
in 24-hour format without
the literal AM or PM
SQL Server '01:01:01.1234567PM' 13:01:01.1234567 A space before AM or PM is

optional.
SQL Server '01AM' 01:00:00.0000000 When only the hour is

specified, all other values are
0.
SQL Server '01 AM' 01:00:00.0000000 A space before AM or PM is

optional.
SQL Server '01:01:01' 01:01:01.0000000 When fractional seconds

precision is not specified,
each position that is defined
by the data type is 0.
ISO 8601 '01:01:01.1234567' 01:01:01.1234567 To comply with ISO 8601,

use 24-hour format, not AM
or PM.
ISO 8601 '01:01:01.1234567 +01:01' 01:01:01.1234567 The optional time zone

difference (TZD) is allowed in
the input but is not stored.
C. Inserting Time String Literal into Columns of Each date and time Date Type
In the following table the first column shows a time string literal to be inserted into a database table column of
the date or time data type shown in the second column. The third column shows the value that will be stored in
the database table column.
VALUE THAT IS STORED IN

INSERTED STRING LITERAL COLUMN DATA TYPE COLUMN DESCRIPTION
'12:12:12.1234567' time(7) 12:12:12.1234567 If the fractional seconds

precision exceeds the value
specified for the column, the
string will be truncated
without error.
'2007-05-07' date NULL Any time value will cause

the INSERT statement to fail.
'12:12:12' smalldatetime 1900-01-01 12:12:00 Any fractional seconds

precision value will cause the
INSERT statement to fail.
VALUE THAT IS STORED IN
INSERTED STRING LITERAL COLUMN DATA TYPE COLUMN DESCRIPTION
'12:12:12.123' datetime 1900-01-01 12:12:12.123 Any second precision longer

than three positions will
cause the INSERT statement
to fail.
'12:12:12.1234567' datetime2(7) 1900-01-01 If the fractional seconds

12:12:12.1234567 precision exceeds the value
without error.
'12:12:12.1234567' datetimeoffset(7) 1900-01-01 If the fractional seconds

12:12:12.1234567 +00:00 precision exceeds the value
without error.
See Also
hierarchyid data type method reference
The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a
hierarchy. A column of type hierarchyid does not automatically represent a tree. It is up to the application to
generate and assign hierarchyid values in such a way that the desired relationship between rows is reflected in
the values.
A value of the hierarchyid data type represents a position in a tree hierarchy. Values for hierarchyid have the
following properties:
Extremely compact
The average number of bits that are required to represent a node in a tree with n nodes depends on the
average fanout (the average number of children of a node). For small fanouts (0-7), the size is about 6*logAn
bits, where A is the average fanout. A node in an organizational hierarchy of 100,000 people with an average
fanout of 6 levels takes about 38 bits. This is rounded up to 40 bits, or 5 bytes, for storage.
Comparison is in depth-first order
Given two hierarchyid values a and b, a<b means a comes before b in a depth-first traversal of the tree.
Indexes on hierarchyid data types are in depth-first order, and nodes close to each other in a depth-first
traversal are stored near each other. For example, the children of a record are stored adjacent to that record.
For more information, see Hierarchical Data (SQL Server).
Support for arbitrary insertions and deletions
By using the GetDescendant method, it is always possible to generate a sibling to the right of any given node,
to the left of any given node, or between any two siblings. The comparison property is maintained when an
arbitrary number of nodes is inserted or deleted from the hierarchy. Most insertions and deletions preserve
the compactness property. However, insertions between two nodes will produce hierarchyid values with a
slightly less compact representation.
The encoding used in the hierarchyid type is limited to 892 bytes. Consequently, nodes which have too many
levels in their representation to fit into 892 bytes cannot be represented by the hierarchyid type.
The hierarchyid type is available to CLR clients as the SqlHierarchyId data type.
Remarks
The hierarchyid type logically encodes information about a single node in a hierarchy tree by encoding the path
from the root of the tree to the node. Such a path is logically represented as a sequence of node labels of all
children visited after the root. A slash starts the representation, and a path that only visits the root is represented
by a single slash. For levels underneath the root, each label is encoded as a sequence of integers separated by
dots. Comparison between children is performed by comparing the integer sequences separated by dots in
dictionary order. Each level is followed by a slash. Therefore a slash separates parents from their children. For
example, the following are valid hierarchyid paths of lengths 1, 2, 2, 3, and 3 levels respectively:
/
/1/
/0.3.-7/
/1/3/
/0.1/0.2/
Nodes can be inserted in any location. Nodes inserted after /1/2/ but before /1/3/ can be represented as /1/2.5/.
Nodes inserted before 0 have the logical representation as a negative number. For example, a node that comes
before /1/1/ can be represented as /1/-1/. Nodes cannot have leading zeros. For example, /1/1.1/ is valid, but
/1/1.01/ is not valid. To prevent errors, insert nodes by using the GetDescendant method.
Data type conversion

The hierarchyid data type can be converted to other data types as follows:
Use the ToString() method to convert the hierarchyid value to the logical representation as a nvarchar(4000)
data type.
Use Read () and Write () to convert hierarchyid to varbinary.
To transmit hierarchyid parameters through SOAP first cast them as strings.
Upgrading databases
When a database is upgraded to SQL Server 2017, the new assembly and the hierarchyid data type will
automatically be installed. Upgrade advisor rules detect any user type or assemblies with conflicting names. The
upgrade advisor will advise renaming of any conflicting assembly, and either renaming any conflicting type, or
using two-part names in the code to refer to that preexisting user type.
If a database upgrade detects a user assembly with conflicting name, it will automatically rename that assembly
and put the database into suspect mode.
If a user type with conflicting name exists during the upgrade, no special steps are taken. After the upgrade, both
the old user type, and the new system type, will exist. The user type will be available only through two-part names.
Using hierarchyid columns in replicated tables

Columns of type hierarchyid can be used on any replicated table. The requirements for your application depend
on whether replication is one directional or bidirectional, and on the versions of SQL Server that are used.
One -directional replication
One-directional replication includes snapshot replication, transactional replication, and merge replication in which
changes are not made at the Subscriber. How hierachyid columns work with one directional replication depends
on the version of SQL Server the Subscriber is running.
A SQL Server 2017 Publisher can replicate hierachyid columns to a SQL Server 2017 Subscriber without
any special considerations.
A SQL Server 2017 Publisher must convert hierarchyid columns to replicate them to a Subscriber that is
running SQL Server Compact or an earlier version of SQL Server. SQL Server Compact and earlier versions
of SQL Server do not support hierarchyid columns. If you are using one of these versions, you can still
replicate data to a Subscriber. To do this, you must set a schema option or the publication compatibility level
(for merge replication) so the column can be converted to a compatible data type.
Column filtering is supported in both of these scenarios. This includes filtering out hierarchyid columns. Row
filtering is supported as long as the filter does not include a hierarchyid column.
Bi-directional replication
Bi-directional replication includes transactional replication with updating subscriptions, peer-to-peer transactional
replication, and merge replication in which changes are made at the Subscriber. Replication lets you configure a
table with hierarchyid columns for bi-directional replication. Note the following requirements and
considerations.
The Publisher and all Subscribers must be running SQL Server 2017.
Replication replicates the data as bytes and does not perform any validation to assure the integrity of the
hierarchy.
The hierarchy of the changes that were made at the source (Subscriber or Publisher) is not maintained when
they replicate to the destination.
The hash values for hierarchyid columns are specific to the database in which they are generated. Therefore,
the same value could be generated on the Publisher and Subscriber, but it could be applied to different rows.
Replication does not check for this condition, and there is no built-in way to partition hierarchyid column
values as there is for IDENTITY columns. Applications must use constraints or other mechanisms to avoid such
undetected conflicts.
It is possible that rows that are inserted on the Subscriber will be orphaned. The parent row of the inserted
row might have been deleted at the Publisher. This results in an undetected conflict when the row from the
Subscriber is inserted at the Publisher.
Column filters cannot filter out non-nullable hierarchyid columns: inserts from the Subscriber will fail
because there is no default value for the hierarchyid column on the Publisher.
Row filtering is supported as long as the filter does not include a hierarchyid column.
See also
Hierarchical Data (SQL Server)
hierarchyid Data Type Method Reference
GetAncestor (Database Engine)
Returns a hierarchyid representing the nth ancestor of this.
Syntax
-- Transact-SQL syntax
child.GetAncestor ( n )
-- CLR syntax
SqlHierarchyId GetAncestor ( int n )
Arguments
n
An int, representing the number of levels to go up in the hierarchy.
Return types
SQL Server return type:hierarchyid
CLR return type:SqlHierarchyId
Remarks
Used to test whether each node in the output has the current node as an ancestor at the specified level.
If a number greater than GetLevel() is passed, NULL is returned.
If a negative number is passed, an exception is raised.
Examples
A. Finding the child nodes of a parent
GetAncestor(1) returns the employees that have david0 as their immediate ancestor (their parent). The following
example uses GetAncestor(1) .
DECLARE @CurrentEmployee hierarchyid

SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\david0'
SELECT OrgNode.ToString() AS Text_OrgNode, *

FROM HumanResources.EmployeeDemo
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee ;
B. Returning the grandchildren of a parent

GetAncestor(2) returns the employees that are two levels down in the hierarchy from the current node. These are
the grandchildren of the current node. The following example uses GetAncestor(2) .

WHERE LoginID = 'adventure-works\ken0'

C. Returning the current row

To return the current node by using GetAncestor(0) , execute the following code.

WHERE LoginID = 'adventure-works\david0'

D. Returning a hierarchy level if a table is not present

GetAncestor returns the selected level in the hierarchy even if a table is not present. For example the following
code designates a current employee and returns the hierarchyid of the ancestor of the current employee without
reference to a table.
DECLARE @CurrentEmployee hierarchyid ;

DECLARE @TargetEmployee hierarchyid ;
SELECT @CurrentEmployee = '/2/3/1.2/5/3/' ;
SELECT @TargetEmployee = @CurrentEmployee.GetAncestor(2) ;
SELECT @TargetEmployee.ToString(), @TargetEmployee ;
E. Calling a common language runtime method

The following code snippet calls the GetAncestor() method.
this.GetAncestor(1)
See also
hierarchyid (Transact-SQL )
GetDescendant (Database Engine)
Returns a child node of the parent.
Syntax
parent.GetDescendant ( child1 , child2 )
-- CLR syntax
SqlHierarchyId GetDescendant ( SqlHierarchyId child1 , SqlHierarchyId child2 )
Arguments
child1
NULL or the hierarchyid of a child of the current node.
child2
NULL or the hierarchyid of a child of the current node.
Return Types
Remarks
Returns one child node that is a descendant of the parent.
If parent is NULL, returns NULL.
If parent is not NULL, and both child1 and child2 are NULL, returns a child of parent.
If parent and child1 are not NULL, and child2 is NULL, returns a child of parent greater than child1.
If parent and child2 are not NULL and child1 is NULL, returns a child of parent less than child2.
If parent, child1, and child2 are not NULL, returns a child of parent greater than child1 and less than child2.
If child1 is not NULL and not a child of parent, an exception is raised.
If child2 is not NULL and not a child of parent, an exception is raised.
If child1 >= child2, an exception is raised.
GetDescendant is deterministic. Therefore, if GetDescendant is called with the same inputs, it will always produce
the same output. However, the exact identity of the child produced can vary depending upon its relationship to the
other nodes, as demonstrated in example C.
Examples
A. Inserting a row as the least descendant node
A new employee is hired, reporting to an existing employee at node /3/1/ . Execute the following code to insert
the new row by using the GetDescendant method without arguments to specify the new rows node as /3/1/1/ :
DECLARE @Manager hierarchyid;

SET @Manager = CAST('/3/1/' AS hierarchyid);
INSERT HumanResources.EmployeeDemo (OrgNode, LoginID, Title, HireDate)

VALUES
(@Manager.GetDescendant(NULL, NULL),
'adventure-works\FirstNewEmployee', 'Application Intern', '3/11/07') ;
B. Inserting a row as a greater descendant node

Another new employee is hired, reporting to the same manager as in example A. Execute the following code to
insert the new row by using the GetDescendant method using the child 1 argument to specify that the node of the
new row will follow the node in example A, becoming /3/1/2/ :
DECLARE @Manager hierarchyid, @Child1 hierarchyid;

SET @Child1 = CAST('/3/1/1/' AS hierarchyid);

VALUES
(@Manager.GetDescendant(@Child1, NULL),
'adventure-works\SecondNewEmployee', 'Application Intern', '3/11/07') ;
C. Inserting a row between two existing nodes

A third employee is hired, reporting to the same manager as in example A. This example inserts the new row to a
node greater than the FirstNewEmployee in example A, and less than the SecondNewEmployee in example B. Execute
the following code by using the GetDescendant method. Use both the child1 argument and the child2 argument to
specify that the node of the new row will become node /3/1/1.1/ :
DECLARE @Manager hierarchyid, @Child1 hierarchyid, @Child2 hierarchyid;


VALUES
(@Manager.GetDescendant(@Child1, @Child2),
'adventure-works\ThirdNewEmployee', 'Application Intern', '3/11/07') ;
After completing examples A, B, and C, the nodes added to the table will be peers with the following hierarchyid
values:
/3/1/1/
/3/1/1.1/
/3/1/2/
Node /3/1/1.1/ is greater than node /3/1/1/ but at the same level in the hierarchy.
D. Scalar examples
SQL Server supports arbitrary insertions and deletions of any hierarchyid nodes. By using GetDescendant(), it is
always possible to generate a node between any two hierarchyid nodes. Execute the following code to generate
sample nodes using GetDescendant :
DECLARE @h hierarchyid = hierarchyid::GetRoot();

DECLARE @c hierarchyid = @h.GetDescendant(NULL, NULL);
SELECT @c.ToString();
DECLARE @c2 hierarchyid = @h.GetDescendant(@c, NULL);
SELECT @c2.ToString();
SET @c2 = @h.GetDescendant(@c, @c2);
SET @c = @h.GetDescendant(@c, @c2);
SELECT @c.ToString();
SET @c2 = @h.GetDescendant(@c, @c2);
E. CLR example
The following code snippet calls the GetDescendant() method:
SqlHierarchyId parent, child1, child2;

parent = SqlHierarchyId.GetRoot();
child1 = parent.GetDescendant(SqlHierarchyId.Null, SqlHierarchyId.Null);
child2 = parent.GetDescendant(child1, SqlHierarchyId.Null);
Console.Write(parent.GetDescendant(child1, child2).ToString());
See also
GetLevel (Database Engine)
Returns an integer that represents the depth of the node this in the tree.
Syntax
node.GetLevel ( )
-- CLR syntax
SqlInt16 GetLevel ( )
Return Types
SQL Server return type:smallint
CLR return type:SqlInt16
Remarks
Used to determine the level of one or more nodes or to filter the nodes to members of a specified level. The root of
the hierarchy is level 0.
GetLevel is very useful for breadth-first search indexes. For more information, see Hierarchical Data (SQL Server).
Examples
A. Returning the hierarchy level as a column
The following example returns a text representation of the hierarchyid, and then the hierarchy level as the
EmpLevel column for all rows in the table:
SELECT OrgNode.ToString() AS Text_OrgNode,

OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeDemo;
B. Returning all members of a hierarchy level

The following example returns all rows in the table at the hierarchy level 2:

WHERE OrgNode.GetLevel() = 2;
C. Returning the root of the hierarchy

The following example returns the root of the hierarchy level:
WHERE OrgNode.GetLevel() = 0;
D. CLR example
The following code snippet calls the GetLevel() method:
this.GetLevel()
See also
GetRoot (Database Engine)
Returns the root of the hierarchy tree. GetRoot() is a static method.
Syntax
hierarchyid::GetRoot ( )
-- CLR syntax
static SqlHierarchyId GetRoot ( )
Return Types
Remarks
Used to determine the root node in a hierarchy tree.
Examples
A. Transact-SQL example
The following example returns the root of the hierarchy tree:

WHERE OrgNode = hierarchyid::GetRoot()
B. CLR example
The following code snippet calls the GetRoot() method:
SqlHierarchyId.GetRoot()
See also
Returns true if this is a descendant of parent.
Syntax
child. IsDescendantOf ( parent )
-- CLR syntax
SqlHierarchyId IsDescendantOf (SqlHierarchyId parent )
Arguments
parent
The hierarchyid node for which the IsDescendantOf test should be performed.
Return Types
SQL Server return type:bit
CLR return type:SqlBoolean
Remarks
Returns true for all the nodes in the sub-tree rooted at parent, and false for all other nodes.
Parent is considered its own descendant.
Examples
A. Using IsDescendantOf in a WHERE clause
The following example returns a manager and the employees that report to the manager:
DECLARE @Manager hierarchyid

SELECT @Manager = OrgNode FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\dylan0'
SELECT * FROM HumanResources.EmployeeDemo

WHERE OrgNode.IsDescendantOf(@Manager) = 1
B. Using IsDescendantOf to evaluate a relationship

The following code declares and populates three variables. It then evaluates the hierarchical relationship and
returns one of two printed results based on the comparison:
DECLARE @Manager hierarchyid, @Employee hierarchyid, @LoginID nvarchar(256)
SELECT @Manager = OrgNode FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\terri0' ;
SELECT @Employee = OrgNode, @LoginID = LoginID FROM HumanResources.EmployeeDemo

WHERE LoginID = 'adventure-works\rob0'
IF @Employee.IsDescendantOf(@Manager) = 1
BEGIN
PRINT 'LoginID ' + @LoginID + ' is a subordinate of the selected Manager.'
END
ELSE
BEGIN
PRINT 'LoginID ' + @LoginID + ' is not a subordinate of the selected Manager.' ;
END
C. Calling a common language runtime method

The following code snippet calls the IsDescendantOf() method.
this.IsDescendantOf(Parent)
See also
Parse (Database Engine)
Parse converts the canonical string representation of a hierarchyid to a hierarchyid value. Parse is called
implicitly when a conversion from a string type to hierarchyid occurs. Acts as the opposite of ToString. Parse() is a
static method.
Syntax
hierarchyid::Parse ( input )
-- This is functionally equivalent to the following syntax
-- which implicitly calls Parse():
CAST ( input AS hierarchyid )
-- CLR syntax
static SqlHierarchyId Parse ( SqlString input )
Arguments
input
Transact-SQL: The character data type value that is being converted.
CLR: The String value that is being evaluated.
Return Types
Remarks
If Parse receives a value that is not a valid string representation of a hierarchyid, an exception is raised. For
example, if char data types contain trailing spaces, an exception is raised.
Examples
A. Converting Transact-SQL values without a table
The following code example uses ToString to convert a hierarchyid value to a string, and Parse to convert a
string value to a hierarchyid.
DECLARE @StringValue AS nvarchar(4000), @hierarchyidValue AS hierarchyid
SET @StringValue = '/1/1/3/'
SET @hierarchyidValue = 0x5ADE
SELECT hierarchyid::Parse(@StringValue) AS hierarchyidRepresentation,

@hierarchyidValue.ToString() AS StringRepresentation ;
GO
hierarchyidRepresentation StringRepresentation
------------------------- -----------------------
0x5ADE /1/1/3/
B. CLR example
The following code snippet calls the Parse() method:
string input = “/1/2/”;

SqlHierarchyId.Parse(input);
See also
Read reads binary representation of SqlHierarchyId from the passed-in BinaryReader and sets the
SqlHierarchyId object to that value. Read cannot be called by using Transact-SQL. Use CAST or CONVERT
instead.
Syntax
void Read( BinaryReader r )
Arguments
r
The BinaryReader object that produces a binary stream corresponding to a binary representation of a
hierarchyid node.
Return types
CLR return type:void
Remarks
Read does not validate its input. If an invalid binary input is given, Read might raise an exception. Or, it might
succeed and produce an invalid SqlHierarchyId object whose methods can either give unpredictable results or
raise an exception.
Read can only be called on a newly created SqlHierarchyId object.
Read is used internally by SQL Server when it is necessary, such as when writing data to hierarchyid column.
Read is also called internally when a conversion is done between varbinary and hierarchyid.
Examples
Byte[] encoding = new byte[] { 0x58 };
MemoryStream stream = new MemoryStream(encoding, false /*not writable*/);
BinaryReader br = new BinaryReader(stream);
SqlHierarchyId hid = new SqlHierarchyId();
hid.Read(br);
See Also
GetReparentedValue (Database Engine)
Returns a node whose path from the root is the path to newRoot, followed by the path from oldRoot to this.
Syntax
node. GetReparentedValue ( oldRoot, newRoot )
-- CLR syntax
SqlHierarchyId GetReparentedValue ( SqlHierarchyId oldRoot , SqlHierarchyId newRoot )
Arguments
oldRoot
A hierarchyid that is the node that represents the level of the hierarchy that is to be modified.
newRoot
A hierarchyid that represents the node that will replace the oldRoot section of the current node in order to move
the node.
Return Types
Remarks
Can be used to modify the tree by moving nodes from oldRoot to newRoot. GetReparentedValue can be used to
move a node of a hierarchy to a new location in the hierarchy. The hierarchyid data type represents but does not
enforce the hierarchical structure. Users must ensure that the hierarchyid is appropriately structured for the new
location. A unique index on the hierarchyid data type can help prevent duplicate entries. For an example of
moving an entire subtree, see Hierarchical Data (SQL Server).
Examples
A. Comparing two node locations
The following example shows the current hierarchyid of a node. It also shows what the hierarchyid of the node
would be if the node were moved to become a descendant of the @NewParent node. It uses the ToString()
method to show the hierarchical relationships.
DECLARE @SubjectEmployee hierarchyid , @OldParent hierarchyid, @NewParent hierarchyid
SELECT @SubjectEmployee = OrgNode FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\gail0' ;
SELECT @OldParent = OrgNode FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\roberto0' ; -- who is /1/1/
SELECT @NewParent = OrgNode FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\wanida0' ; -- who is /2/3/
SELECT OrgNode.ToString() AS Current_OrgNode_AS_Text,

(@SubjectEmployee. GetReparentedValue(@OldParent, @NewParent) ).ToString() AS Proposed_OrgNode_AS_Text,
OrgNode AS Current_OrgNode,
@SubjectEmployee. GetReparentedValue(@OldParent, @NewParent) AS Proposed_OrgNode,
*
WHERE OrgNode = @SubjectEmployee ;
GO
B. Updating a node to a new location

The following example uses GetReparentedValue() in an UPDATE statement to move a node from an old location
to a new location in the hierarchy:
DECLARE @SubjectEmployee hierarchyid , @OldParent hierarchyid, @NewParent hierarchyid

SELECT @SubjectEmployee = OrgNode FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\gail0' ; -- Node /1/1/2/
SELECT @OldParent = OrgNode FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\roberto0' ; -- Node /1/1/
SELECT @NewParent = OrgNode FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\wanida0' ; -- Node /2/3/
UPDATE HumanResources.EmployeeDemo
SET OrgNode = @SubjectEmployee. GetReparentedValue(@OldParent, @NewParent)
WHERE OrgNode = @SubjectEmployee ;
SELECT OrgNode.ToString() AS Current_OrgNode_AS_Text,

*
WHERE LoginID = 'adventure-works\gail0' ; -- Now node /2/3/2/
C. CLR example
The following code snippet calls the GetReparentedValue () method:
this. GetReparentedValue(oldParent, newParent)
See also
Returns a string with the logical representation of this. ToString is called implicitly when a conversion from
hierarchyid to a string type occurs. Acts as the opposite of Parse (Database Engine).
Syntax
node.ToString ( )
-- This is functionally equivalent to the following syntax
-- which implicitly calls ToString():
CAST(node AS nvarchar(4000))
-- CLR syntax
string ToString ( )
Return types
SQL Server return type:nvarchar(4000)
CLR return type:String
Remarks
Returns the logical location in the hierarchy. For example, /2/1/ represents the fourth row ( Microsoft SQL
Server) in the following hierarchical structure of a file system:
/ C:\
/1/ C:\Database Files
/2/ C:\Program Files
/2/1/ C:\Program Files\Microsoft SQL Server
/2/2/ C:\Program Files\Microsoft Visual Studio
/3/ C:\Windows
Examples
A. Transact-SQL example in a table
The following example returns both the OrgNode column as both the hierarchyid data type and in the more
readable string format:
SELECT OrgNode,
OrgNode.ToString() AS Node
ORDER BY OrgNode ;
GO
OrgNode Node
0x /
0x58 /1/
0x5AC0 /1/1/
0x5B40 /1/2/
0x5BC0 /1/3/
0x5C20 /1/4/
...
B. Converting Transact-SQL values without a table

The following code example uses ToString to convert a hierarchyid value to a string, and Parse to convert a
string value to a hierarchyid.
DECLARE @StringValue AS nvarchar(4000), @hierarchyidValue AS hierarchyid

SET @StringValue = '/1/1/3/'
SET @hierarchyidValue = 0x5ADE
SELECT hierarchyid::Parse(@StringValue) AS hierarchyidRepresentation,

@hierarchyidValue.ToString() AS StringRepresentation ;
GO
hierarchyidRepresentation StringRepresentation
------------------------- -----------------------
0x5ADE /1/1/3/
C. CLR example
The following code snippet calls the ToString() method:
this.ToString()
See also
Write writes out a binary representation of SqlHierarchyId to the passed-in BinaryWriter. Write cannot be
called by using Transact-SQL. Use CAST or CONVERT instead.
Syntax
void Write( BinaryWriter w )
Arguments
w
A BinaryWriter object to which the binary representation of this hierarchyid node will be written out.
Return Types
CLR return type:void
Remarks
Write is used internally by SQL Server when it is necessary, such as when loading data from a hierarchyid
column. Write is also called internally when a conversion is done between hierarchyid and varbinary.
Examples
MemoryStream stream = new MemoryStream();
BinaryWriter bw = new BinaryWriter(stream);
hid.Write(bw);
byte[] encoding = stream.ToArray();
See also
Numeric types
SQL Server supports the following numeric types.
In this section
decimal and numeric (Transact-SQL )
float and real (Transact-SQL )
int, bigint, smallint, and tinyint (Transact-SQL )
money and smallmoney (Transact-SQL )
decimal and numeric (Transact-SQL)
Numeric data types that have fixed precision and scale. Decimal and numeric are synonyms and can be used
interchangeably.
Arguments
decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through
10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
p (precision)
The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal
point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.
NOTE
Informatica only supports 16 significant digits, regardless of the precision and scale specified.
s (scale)
The number of decimal digits that will be stored to the right of the decimal point. This number is subtracted from p
to determine the maximum number of digits to the left of the decimal point. The maximum number of decimal
digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be
specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary,
based on the precision.
PRECISION STORAGE BYTES
1-9 5
10-19 9
20-28 13
29-38 17
NOTE
Informatica (connected through the SQL Server PDW Informatica Connector) only supports 16 significant digits, regardless
of the precision and scale specified.
Converting decimal and numeric data

For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale
as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.
In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value,
using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric
value with a precision of 5 and a scale of 3.
Converting from decimal or numeric to float or real can cause some loss of precision. Converting from int,
smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow.
By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower
precision and scale. However, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow
occurs. Loss of only precision and scale is not sufficient to raise an error.
When converting float or real values to decimal or numeric, the decimal value will never have more than 17
decimals. Any float value < 5E -18 will always convert as 0.
Examples
The following example creates a table using the decimal and numeric data types. Values are inserted into each
column and the results are returned by using a SELECT statement.
CREATE TABLE dbo.MyTable

(
MyDecimalColumn decimal(5,2)
,MyNumericColumn numeric(10,5)
);
GO
INSERT INTO dbo.MyTable VALUES (123, 12345.12);
GO
SELECT MyDecimalColumn, MyNumericColumn
FROM dbo.MyTable;
MyDecimalColumn MyNumericColumn
--------------------------------------- ---------------------------------------
123.00 12345.12000
(1 row(s) affected)
See also
float and real (Transact-SQL)
Approximate-number data types for use with floating point numeric data. Floating point data is approximate;
therefore, not all values in the data type range can be represented exactly. The ISO synonym for real is float(24).
Transact-SQL Syntax Conventions
Syntax
float [ (n) ] Where n is the number of bits that are used to store the mantissa of the float number in scientific
notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and
53. The default value of n is 53.
N VALUE PRECISION STORAGE SIZE
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes
NOTE
SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53.
The SQL Server float[(n)] data type complies with the ISO standard for all values of n from 1 through 53. The
synonym for double precision is float(53).
Remarks
DATA TYPE RANGE STORAGE
float - 1.79E+308 to -2.23E-308, 0 and Depends on the value of n

2.23E-308 to 1.79E+308
real - 3.40E + 38 to -1.18E - 38, 0 and 4 Bytes

1.18E - 38 to 3.40E + 38
Converting float and real data

Values of float are truncated when they are converted to any integer type.
When you want to convert from float or real to character data, using the STR string function is usually more
useful than CAST( ). This is because STR enables more control over formatting. For more information, see STR
(Transact-SQL ) and Functions (Transact-SQL ).
Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision
17 digits only. Any value < 5E -18 rounds down to 0.
See also
int, bigint, smallint, and tinyint (Transact-SQL)
Exact-number data types that use integer data. To save space in the database, use the smallest data type that can
reliably contain all possible values. For example, tinyint would be sufficient for a person's age because no one lives
to be more than 255 years old. But tinyint would not be sufficient for a building's age because a building can be
more than 255 years old.
bigint -2^63 (-9,223,372,036,854,775,808) to 8 Bytes

2^63-1 (9,223,372,036,854,775,807)
int -2^31 (-2,147,483,648) to 2^31-1 4 Bytes

(2,147,483,647)
smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes
tinyint 0 to 255 1 Byte
Remarks
The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when
integer values might exceed the range that is supported by the int data type.
bigint fits between smallmoney and int in the data type precedence chart.
Functions return bigint only if the parameter expression is a bigint data type. SQL Server does not automatically
promote other integer data types (tinyint, smallint, and int) to bigint.
Cau t i on
When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint,
tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server
applies when it calculates the data type and precision of the expression results differ depending on whether the
query is autoparameterized or not.
Therefore, similar expressions in queries can sometimes produce different results. When a query is not
autoparameterized, the constant value is first converted to numeric, whose precision is just large enough to hold
the value of the constant, before converting to the specified data type. For example, the constant value 1 is
converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).
When a query is autoparameterized, the constant value is always converted to numeric (10, 0) before converting
to the final data type. When the / operator is involved, not only can the result type's precision differ among similar
queries, but the result value can differ also. For example, the result value of an autoparameterized query that
includes the expression SELECT CAST (1.0 / 7 AS float) , differs from the result value of the same query that is not
autoparameterized, because the results of the autoparameterized query, are truncated to fit into the numeric (10,
0) data type.
Converting integer data

When integers are implicitly converted to a character data type, if the integer is too large to fit into the character
field, SQL Server enters ASCII character 42, the asterisk (*).
Integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type.
The following example shows that when the threshold value is exceeded, the data type of the result changes from
an int to a decimal.
SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2 ;
Result1 Result2
1073741823 1073741824.500000
Examples
The following example creates a table using the bigint, int, smallint, and tinyint data types. Values are inserted
into each column and returned in the SELECT statement.

(
MyBigIntColumn bigint
,MyIntColumn int
,MySmallIntColumn smallint
,MyTinyIntColumn tinyint
);
GO
INSERT INTO dbo.MyTable VALUES (9223372036854775807, 2147483647,32767,255);

GO
SELECT MyBigIntColumn, MyIntColumn, MySmallIntColumn, MyTinyIntColumn
FROM dbo.MyTable;
MyBigIntColumn MyIntColumn MySmallIntColumn MyTinyIntColumn

-------------------- ----------- ---------------- ---------------
9223372036854775807 2147483647 32767 255
(1 row(s) affected)
See also
money and smallmoney (Transact-SQL)
Data types that represent monetary or currency values.
Remarks
money -922,337,203,685,477.5808 to 8 bytes

922,337,203,685,477.5807 (-
922,337,203,685,477.58
to 922,337,203,685,477.58 for
Informatica. Informatica only supports
two decimals, not four.)
smallmoney - 214,748.3648 to 214,748.3647 4 bytes
The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they
represent. For Informatica, the money and smallmoney data types are accurate to a one-hundredth of the
monetary units that they represent.
Use a period to separate partial monetary units, like cents, from whole monetary units. For example, 2.15 specifies
2 dollars and 15 cents.
These data types can use any one of the following currency symbols.
Currency or monetary data does not need to be enclosed in single quotation marks ( ' ). It is important to
remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store
any currency information associated with the symbol, it only stores the numeric value.
Converting money data

When you convert to money from integer data types, units are assumed to be in monetary units. For example, the
integer value of 4 is converted to the money equivalent of 4 monetary units.
The following example converts smallmoney and money values to varchar and decimal data types,
respectively.
DECLARE @mymoney_sm smallmoney = 3148.29,

@mymoney money = 3148.29;
SELECT CAST(@mymoney_sm AS varchar) AS 'SM_MONEY varchar',
CAST(@mymoney AS decimal) AS 'MONEY DECIMAL';

SM_MONEY VARCHAR MONEY DECIMAL
------------------------------ ----------------------
3148.29 3148
(1 row(s) affected)
See also
ALTER TABLE (Transact-SQL ) CAST and CONVERT (Transact-SQL ) CREATE TABLE (Transact-SQL ) Data Types
(Transact-SQL ) DECL ARE @local_variable (Transact-SQL ) SET @local_variable (Transact-SQL ) sys.types (Transact-
SQL )
rowversion (Transact-SQL)
Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is
generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data
type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a
datetime2 data type.
Remarks
Each database has a counter that is incremented for each insert or update operation that is performed on a table
that contains a rowversion column within the database. This counter is the database rowversion. This tracks a
relative time within a database, not an actual time that can be associated with a clock. A table can have only one
rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented
database rowversion value is inserted in the rowversion column. This property makes a rowversion column a
poor candidate for keys, especially primary keys. Any update made to the row changes the rowversion value and,
therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign
keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change
the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate
updates of the index. The rowversion value is incremented with any update statement, even if no row values are
changed. (For example, if a column value is 5, and an update statement sets the value to 5, this action is considered
an update even though there is no change, and the rowversion is incremented.)
timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In
DDL statements, use rowversion instead of timestamp wherever possible. For more information, see Data Type
Synonyms (Transact-SQL ).
The Transact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.
NOTE
The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using
this feature in new development work, and plan to modify applications that currently use this feature.
In a CREATE TABLE or ALTER TABLE statement, you do not have to specify a column name for the timestamp
data type, for example:
CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp);
If you do not specify a column name, the SQL Server Database Engine generates the timestamp column name;
however, the rowversion synonym does not follow this behavior. When you use rowversion, you must specify a
column name, for example:
CREATE TABLE ExampleTable2 (PriKey int PRIMARY KEY, VerCol rowversion) ;

NOTE
Duplicate rowversion values can be generated by using the SELECT INTO statement in which a rowversion column is in the
SELECT list. We do not recommend using rowversion in this manner.
A nonnullable rowversion column is semantically equivalent to a binary(8) column. A nullable rowversion

column is semantically equivalent to a varbinary(8) column.
You can use the rowversion column of a row to easily determine whether the row has had an update statement
ran against it since the last time it was read. If an update statement is ran against the row, the rowversion value is
updated. If no update statements are ran against the row, the rowversion value is the same as when it was
previously read. To return the current rowversion value for a database, use @@DBTS.
You can add a rowversion column to a table to help maintain the integrity of the database when multiple users are
updating rows at the same time. You may also want to know how many rows and which rows were updated
without re-querying the table.
For example, assume that you create a table named MyTest . You populate some data in the table by running the
following Transact-SQL statements.
CREATE TABLE MyTest (myKey int PRIMARY KEY

,myValue int, RV rowversion);
GO
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);
GO
INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);
GO
You can then use the following sample Transact-SQL statements to implement optimistic concurrency control on
the MyTest table during the update.
DECLARE @t TABLE (myKey int);

UPDATE MyTest
SET myValue = 2
OUTPUT inserted.myKey INTO @t(myKey)
WHERE myKey = 1
AND RV = myValue;
IF (SELECT COUNT(*) FROM @t) = 0
BEGIN
RAISERROR ('error changing row with myKey = %d'
,16 -- Severity.
,1 -- State
,1) -- myKey that was changed
END;
myValue is the rowversion column value for the row that indicates the last time that you read the row. This value
must be replaced by the actual rowversion value. An example of the actual rowversion value is
0x00000000000007D3.
You can also put the sample Transact-SQL statements into a transaction. By querying the @t variable in the scope
of the transaction, you can retrieve the updated myKey column of the table without requerying the MyTes t table.
The following is the same example using the timestamp syntax:
CREATE TABLE MyTest2 (myKey int PRIMARY KEY
,myValue int, TS timestamp);
GO
INSERT INTO MyTest2 (myKey, myValue) VALUES (1, 0);
GO
INSERT INTO MyTest2 (myKey, myValue) VALUES (2, 0);
GO
DECLARE @t TABLE (myKey int);
UPDATE MyTest2
SET myValue = 2
OUTPUT inserted.myKey INTO @t(myKey)
WHERE myKey = 1
AND TS = myValue;
IF (SELECT COUNT(*) FROM @t) = 0
BEGIN
RAISERROR ('error changing row with myKey = %d'
,16 -- Severity.
,1 -- State
,1) -- myKey that was changed
END;
See also
DELETE (Transact-SQL )
INSERT (Transact-SQL )
MIN_ACTIVE_ROWVERSION (Transact-SQL )
UPDATE (Transact-SQL )
String and Binary Types
SQL Server supports the following string and binary types.
In This Section
binary and varbinary (Transact-SQL )
char and varchar (Transact-SQL )
nchar and nvarchar (Transact-SQL )
ntext, text, and image (Transact-SQL )
binary and varbinary (Transact-SQL)
Binary data types of either fixed length or variable length.
Arguments
binary [ ( n ) ] Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The
storage size is n bytes.
varbinary [ ( n | max) ] Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the
maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The
data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.
Remarks
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not
specified with the CAST function, the default length is 30.
DATA TYPE USE WHEN ...
binary the sizes of the column data entries are consistent.
varbinary the sizes of the column data entries vary considerably.
varbinary(max) the column data entries exceed 8,000 bytes.
Converting binary and varbinary data

When data is converted from a string data type (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext,
or image) to a binary or varbinary data type of unequal length, SQL Server pads or truncates the data on the
right. When other data types are converted to binary or varbinary, the data is padded or truncated on the left.
Padding is achieved by using hexadecimal zeros.
Converting data to the binary and varbinary data types is useful if binary data is the easiest way to move
around data. Converting any value of any type to a binary value of large enough size and then back to the type,
always results in the same value if both conversions are taking place on the same version of SQL Server. The
binary representation of a value might change from version to version of SQL Server.
You can convert int, smallint, and tinyint to binary or varbinary, but if you convert the binary value back to an
integer value, this value will be different from the original integer value if truncation has occurred. For example,
the following SELECT statement shows that the integer value 123456 is usually stored as a binary 0x0001e240 :
SELECT CAST( 123456 AS BINARY(4) );
However, the following SELECT statement shows that if the binary target is too small to hold the entire value, the
leading digits are silently truncated so that the same number is stored as 0xe240 :
SELECT CAST( 123456 AS BINARY(2) );
The following batch shows that this silent truncation can affect arithmetic operations without raising an error:
DECLARE @BinaryVariable2 BINARY(2);
SET @BinaryVariable2 = 123456;

SET @BinaryVariable2 = @BinaryVariable2 + 1;
SELECT CAST( @BinaryVariable2 AS INT);

GO
The final result is 57921 , not 123457 .
NOTE
Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL
Server.
See also
char and varchar (Transact-SQL)
These data types are of either fixed length or variable length.
Arguments
char [ ( n ) ] Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through
8,000. The storage size is n bytes. The ISO synonym for char is character.
varchar [ ( n | max ) ] Variable-length, non-Unicode string data. n defines the string length and can be a value
from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB ). The storage size is
the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are charvarying or
charactervarying.
Remarks
specified when using the CAST and CONVERT functions, the default length is 30.
Objects that use char or varchar are assigned the default collation of the database, unless a specific collation is
assigned using the COLL ATE clause. The collation controls the code page that is used to store the character data.
If you have sites that support multiple languages, consider using the Unicode nchar or nvarchar data types to
minimize character conversion issues. If you use char or varchar, we recommend the following:
Use char when the sizes of the column data entries are consistent.
Use varchar when the sizes of the column data entries vary considerably.
Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed
8,000 bytes.
If SET ANSI_PADDING is OFF when either CREATE TABLE or ALTER TABLE is executed, a char column that is
defined as NULL is handled as varchar.
When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the
character string, the storage size of n bytes can be less than n characters.
WARNING
Each non-null varchar(max) or nvarchar(max) column requires 24 bytes of additional fixed allocation which counts against
the 8,060 byte row limit during a sort operation. This can create an implicit limit to the number of non-null varchar(max) or
nvarchar(max) columns that can be created in a table.
No special error is provided when the table is created (beyond the usual warning that the maximum row size exceeds the
allowed maximum of 8060 bytes) or at the time of data insertion. This large row size can cause errors (such as error 512)
during some normal operations, such as a clustered index key update, or sorts of the full column set, which users cannot
anticipate until performing an operation.
Converting Character Data

When character expressions are converted to a character data type of a different size, values that are too long for
the new data type are truncated. The uniqueidentifier type is considered a character type for the purposes of
conversion from a character expression, and therefore is subject to the truncation rules for converting to a
character type. See the Examples section that follows.
When a character expression is converted to a character expression of a different data type or size, such as from
char(5) to varchar(5), or char(20) to char(15), the collation of the input value is assigned to the converted value.
If a noncharacter expression is converted to a character data type, the default collation of the current database is
assigned to the converted value. In either case, you can assign a specific collation by using the COLL ATE clause.
NOTE
Code page translations are supported for char and varchar data types, but not for text data type. As with earlier versions
of SQL Server, data loss during code page translations is not reported.
Character expressions that are being converted to an approximate numeric data type can include optional
exponential notation (a lowercase e or uppercase E followed by an optional plus (+) or minus (-) sign and then a
number).
Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal
point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators, such as the thousands
separator in 123,456.00, are not allowed in the string.
Character expressions being converted to money or smallmoney data types can also include an optional decimal
point and dollar sign ($). Comma separators, as in $123,456.00, are allowed.
Examples
A. Showing the default value of n when used in variable declaration.
The following example shows the default value of n is 1 for the char and varchar data types when they are used
in variable declaration.
DECLARE @myVariable AS varchar = 'abc';

DECLARE @myNextVariable AS char = 'abc';
--The following returns 1
SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable);
GO
B. Showing the default value of n when varchar is used with CAST and CONVERT.
The following example shows that the default value of n is 30 when the char or varchar data types are used with
the CAST and CONVERT functions.
DECLARE @myVariable AS varchar(40);

SET @myVariable = 'This string is longer than thirty characters';
SELECT CAST(@myVariable AS varchar);
SELECT DATALENGTH(CAST(@myVariable AS varchar)) AS 'VarcharDefaultLength';
SELECT CONVERT(char, @myVariable);
SELECT DATALENGTH(CONVERT(char, @myVariable)) AS 'VarcharDefaultLength';
C. Converting Data for Display Purposes

The following example converts two columns to character types and applies a style that applies a specific format
to the displayed data. A money type is converted to character data and style 1 is applied, which displays the
values with commas every three digits to the left of the decimal point, and two digits to the right of the decimal
point. A datetime type is converted to character data and style 3 is applied, which displays the data in the format
dd/mm/yy. In the WHERE clause, a money type is cast to a character type to perform a string comparison
operation.
USE AdventureWorks2012;
GO
SELECT BusinessEntityID,
SalesYTD,
CONVERT (varchar(12),SalesYTD,1) AS MoneyDisplayStyle1,
GETDATE() AS CurrentDate,
CONVERT(varchar(12), GETDATE(), 3) AS DateDisplayStyle3
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS varchar(20) ) LIKE '1%';
BusinessEntityID SalesYTD DisplayFormat CurrentDate DisplayDateFormat

---------------- --------------------- ------------- ----------------------- -----------------
278 1453719.4653 1,453,719.47 2011-05-07 14:29:01.193 07/05/11
280 1352577.1325 1,352,577.13 2011-05-07 14:29:01.193 07/05/11
283 1573012.9383 1,573,012.94 2011-05-07 14:29:01.193 07/05/11
284 1576562.1966 1,576,562.20 2011-05-07 14:29:01.193 07/05/11
285 172524.4512 172,524.45 2011-05-07 14:29:01.193 07/05/11
286 1421810.9242 1,421,810.92 2011-05-07 14:29:01.193 07/05/11
288 1827066.7118 1,827,066.71 2011-05-07 14:29:01.193 07/05/11
D. Converting Uniqueidentifer Data

The following example converts a uniqueidentifier value to a char data type.
DECLARE @myid uniqueidentifier = NEWID();

SELECT CONVERT(char(255), @myid) AS 'char';
The following example demonstrates the truncation of data when the value is too long for the data type being
converted to. Because the uniqueidentifier type is limited to 36 characters, the characters that exceed that length
are truncated.
DECLARE @ID nvarchar(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong';

SELECT @ID, CONVERT(uniqueidentifier, @ID) AS TruncatedValue;
String TruncatedValue
-------------------------------------------- ------------------------------------
0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong 0E984725-C51C-4BF4-9960-E1C80E27ABA0
(1 row(s) affected)
See also
nchar and nvarchar (Transact-SQL )
Estimate the Size of a Database
nchar and nvarchar (Transact-SQL)
Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the
UNICODE UCS -2 character set.
Arguments
nchar [ ( n ) ]
Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The
storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still
n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO
synonyms for nchar are national char and national character.
nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max
indicates that the maximum storage size is 2^30-1 characters. The maximum storage size in bytes is 2 GB. The
actual storage size, in bytes, is two times the number of characters entered + 2 bytes. The ISO synonyms for
nvarchar are national char varying and national character varying.
Remarks
specified with the CAST function, the default length is 30.
Use nchar when the sizes of the column data entries are probably going to be similar.
Use nvarchar when the sizes of the column data entries are probably going to vary considerably.
sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that
it is not nullable. sysname is used to reference database object names.
Objects that use nchar or nvarchar are assigned the default collation of the database unless a specific collation is
assigned using the COLL ATE clause.
SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF does not apply to the
nchar or nvarchar data types.
Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the
default code page of the database. This default code page may not recognize certain characters.
NOTE
When prefixing a string constant with the letter N, the implicit conversion will result in a Unicode string if the constant to
convert does not exceed the max length for a Unicode string data type (4,000). Otherwise, the implicit conversion will result
in a Unicode large-value (max).
WARNING
Each non-null varchar(max) or nvarchar(max) column requires 24 bytes of additional fixed allocation, which counts
against the 8,060-byte row limit during a sort operation. These additional bytes can create an implicit limit to the number of
non-null varchar(max) or nvarchar(max) columns in a table. No special error is provided when the table is created
(beyond the usual warning that the maximum row size exceeds the allowed maximum of 8060 bytes) or at the time of data
insertion. This large row size can cause errors (such as error 512) that users may not anticipate during some normal
operations. Two examples of operations are a clustered index key update, or sorts of the full column set.
Converting Character Data

For information about converting character data, see char and varchar (Transact-SQL ).
Examples
(
MyNCharColumn nchar(15)
,MyNVarCharColumn nvarchar(20)
);
GO
INSERT INTO dbo.MyTable VALUES (N'Test data', N'More test data');
GO
SELECT MyNCharColumn, MyNVarCharColumn
FROM dbo.MyTable;
MyNCharColumn MyNVarCharColumn
--------------- --------------------
Test data More test data
(1 row(s) affected)
See also
SET ANSI_PADDING (Transact-SQL )
Collation and Unicode Support
ntext, text, and image (Transact-SQL)
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data.
Unicode data uses the UNICODE UCS -2 character set.
IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid
using these data types in new development work, and plan to modify applications that currently use them. Use
nvarchar(max), varchar(max), and varbinary(max) instead.
Arguments
ntext
Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in
bytes, is two times the string length that is entered. The ISO synonym for ntext is national text.
text
Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1
(2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes.
Depending on the character string, the storage size may be less than 2,147,483,647 bytes.
image
Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
Remarks
The following functions and statements can be used with ntext, text, or image data.
FUNCTIONS STATEMENTS
DATALENGTH (Transact-SQL) READTEXT (Transact-SQL)
PATINDEX (Transact-SQL) SET TEXTSIZE (Transact-SQL)
SUBSTRING (Transact-SQL) UPDATETEXT (Transact-SQL)
TEXTPTR (Transact-SQL) WRITETEXT (Transact-SQL)
TEXTVALID (Transact-SQL)
See also
Collation and Unicode Support
Spatial Types - geography
The geography spatial data type, geography, is implemented as a .NET common language runtime (CLR ) data
type in SQL Server. This type represents data in a round-earth coordinate system. The SQL Server geography
data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.
SQL Server supports a set of methods for the geography spatial data type. This includes methods on geography
that are defined by the Open Geospatial Consortium (OGC ) standard and a set of Microsoft extensions to that
standard.
The error tolerance for the geography methods can be as large as 1.0e-7 * extents. The extents refer to the
approximate maximal distance between points of the geographyobject.
Registering the geography Type

The geography type is predefined and available in each database. You can create table columns of type
geography and operate on geography data in the same manner as you would use other system-supplied types.
Can be used in persisted and non-persisted computed columns.
Examples
A. Showing how to add and query geography data
The following examples show how to add and query geography data. The first example creates a table with an
identity column and a geography column, GeogCol1 . A third column renders the geography column into its Open
Geospatial Consortium (OGC ) Well-Known Text (WKT) representation, and uses the STAsText() method. Two
rows are then inserted: one row contains a LineString instance of geography , and one row contains a Polygon
instance.
IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL

DROP TABLE dbo.SpatialTable;
GO
CREATE TABLE SpatialTable

( id int IDENTITY (1,1),
GeogCol1 geography,
GeogCol2 AS GeogCol1.STAsText() );
GO
INSERT INTO SpatialTable (GeogCol1)

VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326));
INSERT INTO SpatialTable (GeogCol1)

VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653 , -122.348 47.649, -122.348 47.658, -122.358
47.658, -122.358 47.653))', 4326));
GO
B. Returning the intersection of two geography instances

The following example uses the STIntersection() method to return the points where the two previously inserted
geography instances intersect.
DECLARE @geog1 geography;
DECLARE @geog2 geography;
DECLARE @result geography;
SELECT @geog1 = GeogCol1 FROM SpatialTable WHERE id = 1;

SELECT @geog2 = GeogCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geog1.STIntersection(@geog2);
SELECT @result.STAsText();
C. Using geography in a computed column

The following example creates a table with a persisted computed column using a geography type.

GO

(
locationId int IDENTITY(1,1),
location geography,
deliveryArea as location.STBuffer(10) persisted
)
See Also
Spatial Data (SQL Server)
Spatial Types - geometry (Transact-SQL)
The planar spatial data type, geometry, is implemented as a common language runtime (CLR ) data type in SQL
Server. This type represents data in a Euclidean (flat) coordinate system.
SQL Server supports a set of methods for the geometry spatial data type. These methods include methods on
geometry that are defined by the Open Geospatial Consortium (OGC ) standard and a set of Microsoft extensions
to that standard.
The error tolerance for the geometry methods can be as large as 1.0e-7 * extents. The extents refer to the
approximate maximal distance between points of the geometryobject.
Registering the geometry Type

The geometry type is predefined and available in each database. You can create table columns of type geometry
and operate on geometry data in the same manner as you would use other CLR types. Can be used in persisted
and non-persisted computed columns.
Examples
A. Showing how to add and query geometry data
The following two examples show how to add and query geometry data. The first example creates a table with an
identity column and a geometry column, GeomCol1 . A third column renders the geometry column into its Open
Geospatial Consortium (OGC ) Well-Known Text (WKT) representation, and uses the STAsText() method. Two
rows are then inserted: one row contains a LineString instance of geometry , and one row contains a Polygon
instance.

GO

( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO
INSERT INTO SpatialTable (GeomCol1)

VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));
INSERT INTO SpatialTable (GeomCol1)

VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
GO
B. Returning the intersection of two geometry instances

The second example uses the STIntersection() method to return the points where the two previously inserted
geometry instances intersect.
DECLARE @geom1 geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;
SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;

SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();
C. Using geometry in a computed column

The following example creates a table with a persisted computed column using a geometry type.

GO

(
locationId int IDENTITY(1,1),
location geometry,
deliveryArea as location.STBuffer(10) persisted
)
See Also
Spatial Data (SQL Server)
sql_variant (Transact-SQL)
A data type that stores values of various SQL Server-supported data types.
Syntax
sql_variant
Remarks
sql_variant can be used in columns, parameters, variables, and the return values of user-defined functions.
sql_variant enables these database objects to support values of other data types.
A column of type sql_variant may contain rows of different data types. For example, a column defined as
sql_variant can store int, binary, and char values.
sql_variant can have a maximum length of 8016 bytes. This includes both the base type information and the base
type value. The maximum length of the actual base type value is 8,000 bytes.
A sql_variant data type must first be cast to its base data type value before participating in operations such as
addition and subtraction.
sql_variant can be assigned a default value. This data type can also have NULL as its underlying value, but the
NULL values will not have an associated base type. Also, sql_variant cannot have another sql_variant as its base
type.
A unique, primary, or foreign key may include columns of type sql_variant, but the total length of the data values
that make up the key of a specific row should not be more than the maximum length of an index. This is 900 bytes.
A table can have any number of sql_variant columns.
sql_variant cannot be used in CONTAINSTABLE and FREETEXTTABLE.
ODBC does not fully support sql_variant. Therefore, queries of sql_variant columns are returned as binary data
when you use Microsoft OLE DB Provider for ODBC (MSDASQL ). For example, a sql_variant column that
contains the character string data 'PS2091' is returned as 0x505332303931.
Comparing sql_variant Values

The sql_variant data type belongs to the top of the data type hierarchy list for conversion. For sql_variant
comparisons, the SQL Server data type hierarchy order is grouped into data type families.
DATA TYPE HIERARCHY DATA TYPE FAMILY
sql_variant sql_variant
DATA TYPE HIERARCHY DATA TYPE FAMILY
datetime2 Date and time
datetimeoffset Date and time
datetime Date and time
smalldatetime Date and time
date Date and time
time Date and time
float Approximate numeric
real Approximate numeric
decimal Exact numeric
money Exact numeric
smallmoney Exact numeric
bigint Exact numeric
int Exact numeric
smallint Exact numeric
tinyint Exact numeric
bit Exact numeric
nvarchar Unicode
nchar Unicode
varchar Unicode
char Unicode
varbinary Binary
binary Binary
uniqueidentifier Uniqueidentifier
The following rules apply to sql_variant comparisons:

When sql_variant values of different base data types are compared and the base data types are in different
data type families, the value whose data type family is higher in the hierarchy chart is considered the greater of
the two values.
When sql_variant values of different base data types are compared and the base data types are in the same
data type family, the value whose base data type is lower in the hierarchy chart is implicitly converted to the
other data type and the comparison is then made.
When sql_variant values of the char, varchar, nchar, or nvarchar data types are compared, their collations
are first compared based on the following criteria: LCID, LCID version, comparison flags, and sort ID. Each of
these criteria are compared as integer values, and in the order listed. If all of these criteria are equal, then the
actual string values are compared according to the collation.
Converting sql_variant Data

When handling the sql_variant data type, SQL Server supports implicit conversions of objects with other data
types to the sql_variant type. However, SQL Server does not support implicit conversions from sql_variant data
to an object with another data type.
Restrictions
The following table lists the types of values that cannot be stored by using sql_variant:
varchar(max) varbinary(max)
nvarchar(max) xml
text ntext
image rowversion (timestamp)
sql_variant geography
hierarchyid geometry
User-defined types datetimeoffset
Examples
A. Using a sql_variant in a table
The following example, creates a table with a sql_variant data type. Then the example retrieves
SQL_VARIANT_PROPERTY information about the colA value 46279.1 where colB = 1689 , given that tableA has
colA that is of type sql_variant and colB .
CREATE TABLE tableA(colA sql_variant, colB int)

INSERT INTO tableA values ( cast (46279.1 as decimal(8,2)), 1689)
SELECT SQL_VARIANT_PROPERTY(colA,'BaseType') AS 'Base Type',
SQL_VARIANT_PROPERTY(colA,'Precision') AS 'Precision',
SQL_VARIANT_PROPERTY(colA,'Scale') AS 'Scale'
FROM tableA
WHERE colB = 1689
Here is the result set. Note that each of these three values is a sql_variant.
Base Type Precision Scale
--------- --------- -----
decimal 8 2
(1 row(s) affected)
B. Using a sql_variant as a variable

The following example, creates a variable using the sql_variant data type, and then retrieves SQL_VARIANT_PROPERTY
information about a variable named @v1.
DECLARE @v1 sql_variant;

SET @v1 = 'ABC';
SELECT @v1;
SELECT SQL_VARIANT_PROPERTY(@v1, 'BaseType');
SELECT SQL_VARIANT_PROPERTY(@v1, 'MaxLength');
See also
SQL_VARIANT_PROPERTY (Transact-SQL )
table (Transact-SQL)
Is a special data type that can be used to store a result set for processing at a later time. table is primarily used for
temporary storage of a set of rows returned as the result set of a table-valued function. Functions and variables can
be declared to be of type table. table variables can be used in functions, stored procedures, and batches. To declare
variables of type table, use DECL ARE @local_variable.
Applies to: SQL Server ( SQL Server 2008 through current version), Azure SQL Database.
Syntax
table_type_definition ::=
TABLE ( { <column_definition> | <table_constraint> } [ ,...n ] )
<column_definition> ::=
column_name scalar_data_type
[ COLLATE <collation_definition> ]
[ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]
[ ROWGUIDCOL ]
[ column_constraint ] [ ...n ]
<column_constraint> ::=
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| CHECK ( logical_expression )
}
<table_constraint> ::=
{ { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )
| CHECK ( logical_expression )
}
Arguments
table_type_definition
Is the same subset of information that is used to define a table in CREATE TABLE. The table declaration includes
column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY,
UNIQUE KEY, and NULL.
For more information about the syntax, see CREATE TABLE (Transact-SQL ), CREATE FUNCTION (Transact-SQL ),
and DECL ARE @local_variable (Transact-SQL ).
collation_definition
Is the collation of the column that is made up of a Microsoft Windows locale and a comparison style, a Windows
locale and the binary notation, or a Microsoft SQL Server collation. If collation_definition is not specified, the
column inherits the collation of the current database. Or if the column is defined as a common language runtime
(CLR ) user-defined type, the column inherits the collation of the user-defined type.
Remarks
table variables can be referenced by name in the FROM clause of a batch, as shown the following example:
SELECT Employee_ID, Department_ID FROM @MyTableVar;
Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:
SELECT EmployeeID, DepartmentID

FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
m.DepartmentID = Employee.DepartmentID);
table variables provide the following benefits for small-scale queries that have query plans that do not change and
when recompilation concerns are dominant:
A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure,
or batch that it is declared in.
Within its scope, a table variable can be used like a regular table. It may be applied anywhere a table or table
expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table cannot be used in the
following statement:
SELECT select_list INTO table_variable;
table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are
defined.
table variables used in stored procedures cause fewer recompilations of the stored procedures than when
temporary tables are used when there are no cost-based choices that affect performance.
Transactions involving table variables last only for the duration of an update on the table variable. Therefore,
table variables require less locking and logging resources.
Limitations and restrictions

Table variables does not have distribution statistics, they will not trigger recompiles. Therefore, in many cases, the
optimizer will build a query plan on the assumption that the table variable has no rows. For this reason, you should
be cautious about using a table variable if you expect a larger number of rows (greater than 100). Temp tables may
be a better solution in this case. Alternatively, for queries that join the table variable with other tables, use the
RECOMPILE hint, which will cause the optimizer to use the correct cardinality for the table variable.
table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they
should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are
preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and
index selection choices.
Queries that modify table variables do not generate parallel query execution plans. Performance can be affected
when very large table variables, or table variables in complex queries, are modified. In these situations, consider
using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL ). Queries that read table
variables without modifying them can still be parallelized.
Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases,
performance may improve by using temporary tables instead, which support indexes and statistics. For more
information about temporary tables, see CREATE TABLE (Transact-SQL ).
CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined
functions.
Assignment operation between table variables is not supported.
Because table variables have limited scope and are not part of the persistent database, they are not affected by
transaction rollbacks.
Table variables cannot be altered after creation.
Examples
A. Declaring a variable of type table
The following example creates a table variable that stores the values specified in the OUTPUT clause of the
UPDATE statement. Two SELECT statements follow that return the values in @MyTableVar and the results of the
update operation in the Employee table. Note that the results in the INSERTED.ModifiedDate column differ from the
values in the ModifiedDate column in the Employee table. This is because the AFTER UPDATE trigger, which updates
the value of ModifiedDate to the current date, is defined on the Employee table. However, the columns returned
from OUTPUT reflect the data before triggers are fired. For more information, see OUTPUT Clause (Transact-SQL ).
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
B. Creating an inline table -valued function

The following example returns an inline table-valued function. It returns three columns ProductID , Name and the
aggregate of year-to-date totals by store as YTD Total for each product sold to the store.
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
GO
To invoke the function, run this query.
SELECT * FROM Sales.ufn_SalesByStore (602);
See also
CREATE FUNCTION (Transact-SQL )
User-Defined Functions
Use Table-Valued Parameters (Database Engine)
Query Hints (Transact-SQL )
uniqueidentifier (Transact-SQL)
Is a 16-byte GUID.
Remarks
A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:
By using the NEWID or NEWSEQUENTIALID functions.
By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a
hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF -8B86-D011-B42D -00C04FC964FF is a valid
uniqueidentifier value.
Comparison operators can be used with uniqueidentifier values. However, ordering is not implemented by
comparing the bit patterns of the two values. The only operations that can be performed against a
uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT
NULL ). No other arithmetic operators can be used. All column constraints and properties, except IDENTITY, can be
used on the uniqueidentifier data type.
Merge replication and transactional replication with updating subscriptions use uniqueidentifier columns to
guarantee that rows are uniquely identified across multiple copies of the table.
Converting uniqueidentifier Data

The uniqueidentifier type is considered a character type for the purposes of conversion from a character
expression, and therefore is subject to the truncation rules for converting to a character type. That is, when
character expressions are converted to a character data type of a different size, values that are too long for the new
data type are truncated. See the Examples section.
Limitations and restrictions

These tools and features do not support the uniqueidentifier data type:
PolyBase
dwloader loading tool for Parallel Data Warehouse
Examples
The following example converts a uniqueidentifier value to a char data type.
DECLARE @myid uniqueidentifier = NEWID();

SELECT CONVERT(char(255), @myid) AS 'char';
The following example demonstrates the truncation of data when the value is too long for the data type being
converted to. Because the uniqueidentifier type is limited to 36 characters, the characters that exceed that length
are truncated.
DECLARE @ID nvarchar(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong';
SELECT @ID, CONVERT(uniqueidentifier, @ID) AS TruncatedValue;
String TruncatedValue
-------------------------------------------- ------------------------------------
0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong 0E984725-C51C-4BF4-9960-E1C80E27ABA0
(1 row(s) affected)
See also
NEWID (Transact-SQL )
NEWSEQUENTIALID (Transact-SQL )
Updatable Subscriptions for Transactional Replication
xml (Transact-SQL)
Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type.
Syntax
xml ( [ CONTENT | DOCUMENT ] xml_schema_collection )
Arguments
CONTENT
Restricts the xml instance to be a well-formed XML fragment. The XML data can contain multiple zero or more
elements at the top level. Text nodes are also allowed at the top level.
This is the default behavior.
DOCUMENT
Restricts the xml instance to be a well-formed XML document. The XML data must have one and only one root
element. Text nodes are not allowed at the top level.
xml_schema_collection
Is the name of an XML schema collection. To create a typed xml column or variable, you can optionally specify the
XML schema collection name. For more information about typed and untyped XML, see Compare Typed XML to
Untyped XML.
Remarks
The stored representation of xml data type instances cannot exceed 2 gigabytes (GB ) in size.
The CONTENT and DOCUMENT facets apply only to typed XML. For more information see Compare Typed XML
to Untyped XML.
Examples
USE AdventureWorks;
GO
DECLARE @DemographicData xml (Person.IndividualSurveySchemaCollection);
SET @DemographicData = (SELECT TOP 1 Demographics FROM Person.Person);
SELECT @DemographicData;
GO
See Also
xml Data Type Methods
XQuery Language Reference (SQL Server)

Data Types

Uploaded by

Copyright:

Available Formats

Data Types

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Data Types

Uploaded by

Copyright:

Available Formats

Table of Contents

Data type categories

Exact numerics Unicode character strings

Approximate numerics Binary strings

Date and time Other data types

Date and time

Unicode character strings

Other data types

Spatial Geometry Types Spatial Geography Types

Character string constants

Examples of datetime constants are:

float and real constants

The following are examples of money constants:

Specifying Negative and Positive Numbers

Signed decimal expressions:

Signed float expressions:

Signed money expressions:

Implicit and explicit conversion

CAST ( $157.27 AS VARCHAR(10) )

SQL SERVER DATA TYPE VISUAL BASIC DATA TYPE

char, varchar, text, nvarchar, ntext String

decimal, numeric String

binary, varbinary, image One-dimensional Byte() array

money, smallmoney Currency

datetime, smalldatetime Date

Anything set to NULL Variant set to Null

VISUAL BASIC DATA TYPE SQL SERVER DATA TYPE

Long, Integer, Byte, Boolean, Object int

Double, Single float

String with 4000 characters or less varchar/nvarchar

String with more than 4000 characters text/ntext

One-dimensional Byte() array with 8000 bytes or less varbinary

One-dimensional Byte() array with more than 8000 bytes image

SYNONYM SQL SERVER SYSTEM DATA TYPE

Binary varying varbinary

char varying varchar

character varying( n ) varchar(n)

Double precision float

float[(n)] for n = 1-7 real

float[(n)] for n = 8-15 float

national character( n ) nchar(n)

national char( n ) nchar(n)

national character varying( n ) nvarchar(n)

national char varying( n ) nvarchar(n)

national text ntext

OPERATION RESULT PRECISION RESULT SCALE *

e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)

e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)

e1 { UNION | EXCEPT | INTERSECT } e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)

e1 % e2 min(p1-s1, p2 -s2) + max( s1,s2 ) max(s1, s2)

select cast(0.0000009000 as decimal(30,20)) * cast(1.0000000000 as decimal(30,20)) [decimal 38,17]

select cast(0.0000009000 as decimal(30,10)) * cast(1.0000000000 as decimal(30,10)) [decimal(38, 6)]

Usage DECLARE @MyDate date

CREATE TABLE Table1 ( Column1 date )

Default string literal format YYYY-MM-DD

Range 0001-01-01 through 9999-12-31 (1582-10-15 through

January 1, 1 CE through December 31, 9999 CE (October 15,

MM is two digits from 01 to 12 that represent a month in the

DD is two digits from 01 to 31, depending on the month, that