Example
The following script uses the CONVERT () function to display different formats. We will use the GETDATE () function to obtain the current date/time:
The code is as follows: |
Copy code |
CONVERT (VARCHAR (19), GETDATE ()) CONVERT (VARCHAR (10), GETDATE (), 110) CONVERT (VARCHAR (11), GETDATE (), 106) CONVERT (VARCHAR (24), GETDATE (), 113) The result is similar: Dec 29 2008 PM 12-29-2008 29 Dec 08 29 Dec 2008 16:25:46. 635 |
Convert the date format using the convert function in SQLserver 2008-01-15 15: 51SQLserver convert the date format using the convert function
The default datetime format of the Chinese version of SQL Server is yyyy-mm-dd Thh: mm: ss. mmm.
For example:
Select getdate ()
11:06:08. 177
I sorted out the date format conversion methods that may be frequently used in SQL Server:
Example:
Convert function application of SqlServer
The code is as follows: |
Copy code |
Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2006 AM Select CONVERT (varchar (100), GETDATE (), 1): 05/16/06 Select CONVERT (varchar (100), GETDATE (), 2): 06.05.16 Select CONVERT (varchar (100), GETDATE (), 3): 16/05/06 Select CONVERT (varchar (100), GETDATE (), 4): 16.05.06 Select CONVERT (varchar (100), GETDATE (), 5): 16-05-06 Select CONVERT (varchar (100), GETDATE (), 6): 16 05 06 Select CONVERT (varchar (100), GETDATE (), 7): 05 16, 06 Select CONVERT (varchar (100), GETDATE (), 8): 10:57:46 Select CONVERT (varchar (100), GETDATE (), 9): 05 16 2006 10: 57: 46: 827AM Select CONVERT (varchar (100), GETDATE (), 10): 05-16-06 Select CONVERT (varchar (100), GETDATE (), 11): 06/05/16 Select CONVERT (varchar (100), GETDATE (), 12): 060516 Select CONVERT (varchar (100), GETDATE (), 13): 16 05 2006 10: 57: 46: 937 Select CONVERT (varchar (100), GETDATE (), 14): 10: 57: 46: 967 Select CONVERT (varchar (100), GETDATE (), 20): 10:57:47 Select CONVERT (varchar (100), GETDATE (), 21): 10:57:47. 157 Select CONVERT (varchar (100), GETDATE (), 22): 05/16/06 10:57:47 AM Select CONVERT (varchar (100), GETDATE (), 23): Select CONVERT (varchar (100), GETDATE (), 24): 10:57:47 Select CONVERT (varchar (100), GETDATE (), 25): 10:57:47. 250 Select CONVERT (varchar (100), GETDATE (), 100): 05 16 2006 AM Select CONVERT (varchar (100), GETDATE (), 101): 05/16/2006 Select CONVERT (varchar (100), GETDATE (), 102): 2006.05.16 Select CONVERT (varchar (100), GETDATE (), 103): 16/05/2006 Select CONVERT (varchar (100), GETDATE (), 104): 16.05.2006 Select CONVERT (varchar (100), GETDATE (), 105): 16-05-2006 Select CONVERT (varchar (100), GETDATE (), 106): 16 05 2006 Select CONVERT (varchar (100), GETDATE (), 107): 05 16,200 6 Select CONVERT (varchar (100), GETDATE (), 108): 10:57:49 Select CONVERT (varchar (100), GETDATE (), 109): 05 16 2006 10: 57: 49: 437AM Select CONVERT (varchar (100), GETDATE (), 110): 05-16-2006 Select CONVERT (varchar (100), GETDATE (), 111): 2006/05/16 Select CONVERT (varchar (100), GETDATE (), 112): 20060516 Select CONVERT (varchar (100), GETDATE (), 113): 16 05 2006 10: 57: 49: 513 Select CONVERT (varchar (100), GETDATE (), 114): 10: 57: 49: 547 Select CONVERT (varchar (100), GETDATE (), 120): 10:57:49 Select CONVERT (varchar (100), GETDATE (), 121): 10:57:49. 700 Select CONVERT (varchar (100), GETDATE (), 126): 2006-05-16T10: 57: 49.827 Select CONVERT (varchar (100), GETDATE (), 130): 18 ???? ?????? 1427 10: 57: 49: 907AM Select CONVERT (varchar (100), GETDATE (), 131): 18/04/1427 10: 57: 49: 920AM |
The rules are as follows:
1 101 US mm/dd/yyyy
2 102 ANSI yy. mm. dd
3 103 UK/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 value + millisecond mon dd yyyy hh: mi: ss: mmmAM (or PM)
10 110 US mm-dd-yy
11 111 yy/mm/dd in Japan
12 112 ISO yymmdd
-13 or 113 (*) European default value + millisecond dd mon yyyy hh: mm: ss: mmm (24 h)
14 114-hh: mi: ss: mmm (24 h)
-20 or 120 (*) ODBC specification yyyy-mm-dd hh: mm: ss [. fff]
-21 or 121 (*) ODBC specifications (in milliseconds) yyyy-mm-dd hh: mm: ss [. fff]
-126 (***) ISO8601 yyyy-mm-dd Thh: mm: ss: mmm (without spaces)
-130 * Kuwait dd mon yyyy hh: mi: ss: mmmAM
-131 * Kuwait dd/mm/yy hh: mi: ss: mmmAM
The format conversion and calculation code between fields in Emaker can be added to "format conversion (read)" and "format conversion (write)" in the attribute )"
The "field" position in the table field settings can also be flexibly added to the function. For example: 'AF' + ID or ID + '/' + PWD, convert (varchar (50), F1)
, Convert (int, %)-19110000 (read)
Convert (char (8), convert (int, %) + 19110000) (write)
Implements the total calculation of rows and so on. Add: % system variable % to call the system variable set in the Emaker system.
AvailableStyleValue:
Style ID |
Style format |
100 or 0 |
Mon dd yyyy hh: miAM (or PM) |
101 |
Mm/dd/yy |
102 |
Yy. mm. dd |
103 |
Dd/mm/yy |
104 |
Dd. mm. yy |
105 |
Dd-mm-yy |
106 |
Dd mon yy |
107 |
Mon dd, yy |
108 |
Hh: mm: ss |
109 or 9 |
Mon dd yyyy hh: mi: ss: mmmAM (or PM) |
110 |
Mm-dd-yy |
111 |
Yy/mm/dd |
112 |
Yymmdd |
113 or 13 |
Dd mon yyyy hh: mm: ss: mmm (24 h) |
114 |
Hh: mi: ss: mmm (24 h) |
120 or 20 |
Yyyy-mm-dd hh: mi: ss (24 h) |
121 or 21 |
Yyyy-mm-dd hh: mi: ss. mmm (24 h) |
126 |
Yyyy-mm-ddThh: mm: ss. mmm (no space) |
130 |
Dd mon yyyy hh: mi: ss: mmmAM |
131 |
Dd/mm/yy hh: mi: ss: mmmAM |