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



Comments