Convert a date format using the CONVERT function in SQL Server

Convert a date format using the CONVERT function in SQL Server

2008-01-23 15:47

Convert date format with convert function in SQL Server 2008-01-15 convert date format with convert function in 15:51sqlserver

The default date field for the Chinese version of SQL Server datetime format is YYYY-MM-DD Thh:mm:ss.mmm

For example:

Select GETDATE ()

2004-09-12 11:06:08.177

Organize a date format conversion method that you might often use in SQL Server:

Examples are as follows:

Select CONVERT (varchar, GETDATE (), 120)

2004-09-12 11:06:08

Select Replace (replace (CONVERT (varchar, GETDATE (), 120), '-', '), ', '), ': ', ')

20040912110608

Select CONVERT (varchar), GETDATE (), 111)

2004/09/12

Select CONVERT (varchar), GETDATE (), 112)

20040912

Select CONVERT (varchar), GETDATE (), 102)

2004.09.12

Select CONVERT (varchar), GETDATE (), 101)

09/12/2004

Select CONVERT (varchar), GETDATE (), 103)

12/09/2004

Select CONVERT (varchar), GETDATE (), 104)

12.09.2004

Select CONVERT (varchar), GETDATE (), 105)

12-09-2004

Select CONVERT (varchar), GETDATE (), 106)

12 09 2004

Select CONVERT (varchar), GETDATE (), 107)

09 12, 2004

Select CONVERT (varchar), GETDATE (), 108)

11:06:08

Select CONVERT (varchar), GETDATE (), 109)

09 12 2004 1

Select CONVERT (varchar), GETDATE (), 110)

09-12-2004

Select CONVERT (varchar), GETDATE (), 113)

12 09 2004 1

Select CONVERT (varchar), GETDATE (), 114)

11:06:08.177

Select GETDATE ()

Results: 2003-12-28 16:52:00.107

Select CONVERT (char (8), GETDATE (), 112)

Results: 20031228

Select CONVERT (char (8), GETDATE (), 108)

Result: 16:52:00

Select CONVERT (char (8), GETDATE (), 112)

|

refers to the date format

The rules are as follows:

1 101 US MM/DD/YYYY

2 102 ANSI Yy.mm.dd

3 103 UK/France Dd/mm/yy

4 104 German Dd.mm.yy

5 105 Italy Dd-mm-yy

6 106-dd Mon yy

7 107-mon DD, yy

8 108-HH:MM:SS

-9 or 109 (*) Default value + MS Mon dd yyyy hh:mi:ss:mmmAM (or PM)

10 110 US Mm-dd-yy

11 111 Japan YY/MM/DD

ISO YYMMDD

-13 or 113 (*) European default + MS DD Mon yyyy hh:mm:ss:mmm (24h)

114-hh:mi:ss:mmm (24h)

-20 or (*) ODBC specification YYYY-MM-DD HH:MM:SS[.FFF]

-21 or 121 (*) ODBC specification (with milliseconds) Yyyy-mm-dd HH:MM:SS[.FFF]

-126 (* *) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm (excluding spaces)

-130* Kuwait DD Mon yyyy Hh:mi:ss:mmmAM

-131* Kuwait Dd/mm/yy Hh:mi:ss:mmmAM

About the format conversion of fields in Emaker and the operation code between fields can be added to the attribute "format conversion (read out)" and "format conversion (write)", the "field" position in the table field settings can also be flexible add function. For example: ' AF ' +id or id+ '/' +pwd, convert (varchar (), F1),

Convert (int,%)-19110000 (read out)

Convert (char (8), convert (int,%) +19110000) (write)

Implement the total operation of the row, and so on. Add:% system variable%, the system variable that is set in the Emaker system is called.

Convert a date format using the CONVERT function in SQL Server