Finding current date time from SQL Server programmatically (using TSQL) and changing the date time style

Using any of the below option returns the current date time from SQL Server. As all of them internally use the same mechanism, there is no notable performance difference among these options.

Option 1: GETDATE()

Example use: select GETDATE()

Option 2: CURRENT_TIMESTAMP

Example use: CURRENT_TIMESTAMP

Option 3: {fn NOW()}

Example use: select {fn NOW()}

All of them will return output as below:

2010-05-12 14:41:45.240

You can use them in your code as needed. However my personal favorite is GETDATE(). Also if you need to customize/change the output for a specific format (datetime conversion), you can do it easily with GETDATE().

For example, if you need the output in mm-dd-yy format, use below code:

SELECT CONVERT(VARCHAR(20),GETDATE(),110)

You can reformat the output in many other way as per below table (only replace 110 with the appropriate number from below table to get desired output):

Without century (yy)	With century (yyyy)	Standard	Output
-	0 or 100 (1,2)	Default	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yyyy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106(1)	-	dd mon yy
7	107(1)	-	Mon dd, yy
8	108	-	hh:mi:ss
-	9 or 109 (1,2)	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 yyyymmdd
-	13 or 113 (1,2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
-	127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
-	130 (1,2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	Hijri (5)	dd/mm/yy hh:mi:ss:mmmAM

For more details with date time conversion, please refer to http://msdn.microsoft.com/en-us/library/ms187928.aspx