如何将此查询的输出格式化为year...second？
如何将此查询的输出格式化为year...second？
提问于 2018-10-29 19:38:48
我有一个查询，给@secs一些秒，它返回一个包含年份、月、周、天、小时、分钟和秒的表。
举个例子：
declare @secs int
select @secs = 19475298
SELECT FLOOR ( UpTime / 31207680 ) AS YEARS
, FLOOR( (UpTime / 2600640 ) - FLOOR ( UpTime / 31207680 ) * 12 ) AS MONTHS
, FLOOR( (UpTime / 604800 ) - FLOOR ( UpTime / 2600640 ) * 4.3 ) AS WEEKS
, FLOOR( (UpTime / 86400 ) - FLOOR( UpTime / 604800 ) * 7 ) AS DAYS
, FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 ) AS HOURS
, FLOOR( ( UpTime / 60 ) - FLOOR( UpTime / 3600 ) * 60 ) AS MINUTES
, UpTime - FLOOR( UpTime / 60 ) * 60 AS SECONDS
FROM (
select @secs as Uptime
) AS RadheX这给了我：
如何将所有这些列都放在一个字符串中，每个列都有2位数字？
在上面的例子中，结果将是00:07:01:01:09:48:18
回答 1
Database Administration用户
回答已采纳
发布于 2018-10-29 19:58:27
您可以尝试向右(‘0’+强制转换(表达式为varChar(3))，2)如下：
SELECT Right('0' + Cast( FLOOR ( UpTime / 31207680 ) as varChar(3)), 2) + ':' -- YEARS
+ Right('0' + Cast( FLOOR( (UpTime / 2600640 ) - FLOOR ( UpTime / 31207680 ) * 12 ) as varChar(3)), 2) + ':' -- MONTHS
+ Right('0' + Cast( FLOOR( (UpTime / 604800 ) - FLOOR ( UpTime / 2600640 ) * 4.3 ) as varChar(3)), 2) + ':' -- WEEKS
+ Right('0' + Cast( FLOOR( (UpTime / 86400 ) - FLOOR( UpTime / 604800 ) * 7 ) as varChar(3)), 2) + ':' -- DAYS
+ Right('0' + Cast( FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 ) as varChar(3)), 2) + ':' -- HOURS
+ Right('0' + Cast( FLOOR( ( UpTime / 60 ) - FLOOR( UpTime / 3600 ) * 60 ) as varChar(3)), 2) + ':' -- MINUTES
+ Right('0' + Cast( UpTime - FLOOR( UpTime / 60 ) * 60 as varChar(3)), 2) -- SECONDS
FROM (
select @secs as Uptime
) AS RadheX页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/221286
复制相关文章
相似问题
腾讯云开发者
