Suppose I have 90 seconds. If I want to display the result in terms of minutes and second, I do it by using
select Time= '0' + CAST( 90/60 as varchar(2)) + ':' + CAST( 90%60 as varchar(2))
The output is
Time
01:30
开发者_Python百科I have appended 0(zero) because if you do a select getdate()
the output will be
yyyy-mm-dd hh:mm:ss:ms
What is the standard way and recommended practice to do such a conversion?
Thanks
With hours:
SELECT CONVERT(CHAR(8),DATEADD(second,90,0),108)
00:01:30
Ignoring hours:
SELECT RIGHT(CONVERT(CHAR(8),DATEADD(second,90,0),108),5)
01:30
Try this:
select convert(varchar(10), dateadd(second, 15794, 0), 108)
One of the first things I do on a fresh SQL database is add a Timespan
function similar to this one (although I tend to include days and milliseconds as well):
CREATE FUNCTION dbo.TimeSpan
(
@Hours int,
@Minutes int,
@Seconds int
)
RETURNS datetime
AS BEGIN
RETURN DATEADD(SS, @Hours * 3600 + @Minutes * 60 + @Seconds, 0)
END
Then you can format this however you want:
SELECT SUBSTRING(CONVERT(char(8), dbo.TimeSpan(0, 0, 90), 108), 4, 5)
It might look more complicated at first, but the ability to reuse the TimeSpan
function comes in very handy over time. For me it feels like a hack to always be writing DATEADD
calls against 0
or '1753-01-01'
.
精彩评论