SQLServer 时间格式化

来源：互联网发布：中信证券软件下载编辑：程序博客网时间：2024/06/06 09:48

背景知识：

SQL Server自带的Convert函数，可以将日期类型的字段按照指定的格式转换为字符串格式，您可以将以下脚本复制到查询分析器执行；

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 19 not 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 125 not 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

输出结果：

适用场景：

适用于按时间做数据统计，如：按时间段统计用户登录记录，精确到分钟；

SELECT SUBSTRING(CONVERT(NVARCHAR(20), loginTime, 20), 0, 17) 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(20), loginTime, 20), 0, 17)
ORDER BY
SUBSTRING(CONVERT(NVARCHAR(20), loginTime, 20), 0, 17) ASC

博客原文:http://blog.csdn.net/a497785609/article/details/51837561

0 0