SQL server convert () function usage

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