以下是您可以根据自己的需求使用不同的方式来提取它。
使用时间数据类型:
DECLARE @Time Time = '15:04:46.217'
SELECT --'3:04PM'
CONVERT(VarChar(7), @Time, 0),
--' 3:04PM' --Leading Space.
RIGHT(' ' + CONVERT(VarChar(7), @Time, 0), 7),
--' 3:04 PM' --Space before AM/PM.
STUFF(RIGHT(' ' + CONVERT(VarChar(7), @Time, 0), 7), 6, 0, ' '),
--'03:04 PM' --Leading Zero. This answers the question above.
STUFF(RIGHT('0' + CONVERT(VarChar(7), @Time, 0), 7), 6, 0, ' ')
--'03:04 PM' --This only works in SQL Server 2012 and above. :)
,FORMAT(CAST(@Time as DateTime), 'hh:mm tt')--Comment out for SS08 or less.
使用 DateTime 数据类型:
DECLARE @Date DateTime = '2016-03-17 15:04:46.217'
SELECT --' 3:04PM' --No space before AM/PM.
RIGHT(CONVERT(VarChar(19), @Date, 0), 7),
--' 3:04 PM' --Space before AM/PM.
STUFF(RIGHT(CONVERT(VarChar(19), @Date, 0), 7), 6, 0, ' '),
--'3:04 PM' --No Leading Space.
LTRIM(STUFF(RIGHT(CONVERT(VarChar(19), @Date, 0), 7), 6, 0, ' ')),
--'03:04 PM' --Leading Zero.
STUFF(REPLACE(RIGHT(CONVERT(VarChar(19), @Date, 0), 7), ' ', '0'), 6, 0, ' ')
--'03:04 PM' --This only works in SQL Server 2012 and above. :)
,FORMAT(@Date, 'hh:mm tt')--Comment line out for SS08 or less.