Talking about time format of SQL Server

SQL Server time format

Background knowledge:

SQL server comes with a convert function that converts a field of a date type to a string format as specified, and you can copy the following script to Query Analyzer execution;

DECLARE @now datetime

SET @now = GETDATE ()

Select convert (nvarchar (MAX), @now, 0) as output, 0 as style

Union select CONVERT (nvarchar (MAX), @now, 1), 1

Union select CONVERT (nvarchar (MAX), @now, 2), 2

Union select CONVERT (nvarchar (MAX), @now, 3), 3

Union select CONVERT (nvarchar (MAX), @now, 4), 4

Union select CONVERT (nvarchar (MAX), @now, 5), 5

Union select CONVERT (nvarchar (MAX), @now, 6), 6

Union select CONVERT (nvarchar (MAX), @now, 7), 7

Union select CONVERT (nvarchar (MAX), @now, 8), 8

Union select CONVERT (nvarchar (MAX), @now, 9), 9

Union select CONVERT (nvarchar (MAX), @now, 10), 10

Union select CONVERT (nvarchar (MAX), @now, 11), 11

Union select CONVERT (nvarchar (MAX), @now, 12), 12

Union select CONVERT (nvarchar (MAX), @now, 13), 13

Union select CONVERT (nvarchar (MAX), @now, 14), 14

--15 to valid

Union select CONVERT (nvarchar (MAX), @now, 20), 20

Union select CONVERT (nvarchar (MAX), @now, 21), 21

Union select CONVERT (nvarchar (MAX), @now, 22), 22

Union select CONVERT (nvarchar (MAX), @now, 23), 23

Union select CONVERT (nvarchar (MAX), @now, 24), 24

Union select CONVERT (nvarchar (MAX), @now, 25), 25

--26 not valid

Union select CONVERT (nvarchar (MAX), @now, 100), 100

Union select CONVERT (nvarchar (MAX), @now, 101), 101

Union select CONVERT (nvarchar (MAX), @now, 102), 102

Union select CONVERT (nvarchar (MAX), @now, 103), 103

Union select CONVERT (nvarchar (MAX), @now, 104), 104

Union select CONVERT (nvarchar (MAX), @now, 105), 105

Union select CONVERT (nvarchar (MAX), @now, 106), 106

Union select CONVERT (nvarchar (MAX), @now, 107), 107

Union select CONVERT (nvarchar (MAX), @now, 108), 108

Union select CONVERT (nvarchar (MAX), @now, 109), 109

Union select CONVERT (nvarchar (MAX), @now, 110), 110

Union select CONVERT (nvarchar (MAX), @now, 111), 111

Union select CONVERT (nvarchar (MAX), @now, 112), 112

Union select CONVERT (nvarchar (MAX), @now, 113), 113

Union select CONVERT (nvarchar (MAX), @now, 114), 114

Union select CONVERT (nvarchar (MAX), @now, 120), 120

Union select CONVERT (nvarchar (MAX), @now, 121), 121

--122 to valid

Union select CONVERT (nvarchar (MAX), @now, 126), 126

Union select CONVERT (nvarchar (MAX), @now, 127), 127

--128, 129 not valid

Union select CONVERT (nvarchar (MAX), @now, 130), 130

Union select CONVERT (nvarchar (MAX), @now, 131), 131

--132 not valid

ORDER BY Style

Output Result:

　　

Applicable scenarios:

Applicable to data statistics by time, such as: by the time period to count the user login record, accurate to the minute;

SELECT SUBSTRING (CONVERT (NVARCHAR), Logintime, 0) as Date, COUNT (*) as Count

From Userlogin

WHERE logintime between ' 2016-7-1 00:16:02 ' and ' 2016-7-6 10:16:09 '

GROUP by

SUBSTRING (CONVERT (NVARCHAR), Logintime, 20), 0, 17)

ORDER by

SUBSTRING (CONVERT (NVARCHAR), Logintime, 0) ASC

"Wealth Hotline: 400-189-0298" Beijing Mei Yuan ( http://www.meiyuanxing.com/ ) Petrochemical Management Investment Co., Ltd. is a commitment to provide investors with professional Spot Heavy Oil Trading platform, trading account opening services, while providing investment consulting, market analysis, crude oil price inquiry, investment strategy, oil policy and other services companies. Beijing Mei Yuan Petrochemical Investment Management Co., Ltd. to spot heavy oil products trading as the main business, as well as spot heavy oil investment consulting and economic information consulting business.

Talking about time format of SQL Server