Date Functions --- Sqlserver

Date ADD Function()

Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality. 

Syntax

Using CAST:

CAST (expression AS datatype)
 

Using CONVERT:

CONVERT ( datatype [ ( length ) ] , expression [ , style ] )

Arguments

data_type

Is the target system-supplied data type, including bigint and sql_variant User-defined data types cannot be used. For more information about available data types, see Data Types.

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

style

Is the style of date format used to convert date time or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.

In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

Without century (yy)	With century (yyyy)	Standard	Input/Output**
-	0 or 100 (*)	Default	mon dd yyyy hh:miAM (or PM)
1	101	USA	mm/dd/yy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106	-	dd mon yy
7	107	-	Mon dd, yy
8	108	-	hh:mm:ss
-	9 or 109 (*)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd
-	13 or 113 (*)	Europe default + milliseconds	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (*)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (*)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126(***)	ISO8601	yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
-	130*	Hijri****	dd mon yyyy hh:mi:ss:mmmAM
-	131*	Hijri****	dd/mm/yy hh:mi:ss:mmmAM