Date/Time Datatypes

Date/Time Datatypes in SQL Server (Transact-SQL):

Data Type Syntax	Maximum Size	Explanation (if applicable)
DATE	Values range from '0001-01-01' to '9999-12-31'.	Displayed as 'YYYY-MM-DD'
DATETIME	Date values range from '1753-01-01 00:00:00' to '9999-12-31 23:59:59'. Time values range from '00:00:00' to '23:59:59:997'	Displayed as 'YYYY-MM-DD hh:mm:ss[.mmm]'
DATETIME2(fractional seconds precision)	Date values range from '0001-01-01' to '9999-12-31'. Time values range from '00:00:00' to '23:59:59:9999999'.	Displayed as 'YYYY-MM-DD hh:mm:ss[.fractional seconds]'
SMALLDATETIME	Date values range from '1900-01-01' to '2079-06-06'. Time values range from '00:00:00' to '23:59:59'.	Displayed as 'YYYY-MM-DD hh:mm:ss'
TIME	Values range from '00:00:00.0000000' to '23:59:59.9999999'	Displayed as 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]'
DATETIMEOFFSET(fractional seconds precision)	Date values range from '0001-01-01' to '9999-12-31'. Time values range from '00:00:00' to '23:59:59:9999999'. Time zone offset range from -14:00 to +14:00.	Displayed as 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' [{+|-}hh:mm]

                                                                                                                                        

                                      String Datatypes

The following are the String Datatypes in SQL Server (Transact-SQL):

Data Type Syntax	Maximum     Size	Explanation
CHAR(size)	Maximum size of 8,000 characters.	Where size is the number of characters to store. Fixed-length. Space padded on right to equal size characters. Non-Unicode data.
VARCHAR(size) or VARCHAR(max)	Maximum size of 8,000 or max characters.	Where size is the number of characters to store. Variable-length. If max is specified, the maximum number of characters is 2GB. Non-Unicode data.
TEXT	Maximum size of 2GB.	Variable-length. Non-Unicode data.
NCHAR(size)	Maximum size of 4,000 characters.	Fixed-length. Unicode data.
NVARCHAR(size) or NVARCHAR(max)	Maximum size of 4,000 or max characters.	Where size is the number of characters to store. Variable-length. If max is specified, the maximum number of characters is 2GB. Unicode data.
NTEXT	Maximum size of 1,073,741,823 bytes.	Variable length. Unicode data.
BINARY(size)	Maximum size of 8,000 characters.	Where size is the number of characters to store. Fixed-length. Space padded on right to equal size characters. Binary data.
VARBINARY(size) or VARBINARY(max)	Maximum size of 8,000 or max characters.	Where size is the number of characters to store. Variable-length. If max is specified, the maximum number of characters is 2GB. Non-Binary data.
IMAGE	Maximum size of 2GB.	Variable length . Binary data.