How to convert a SQL Bignit column into Days:Hours:Minutes:Seconds

km0tfn4u 于 2023-02-28 发布在其他

关注(0)|答案(2)|浏览(133)

How do I convert a sql bignit column in a table into days:hours:minutes:seconds

For example: (below) is the targeted column from the table and two rows of data:

time_Left
        516542000
        604744000

How do I convert or Cast all the data in the column to display as Days:Hours:Minutes:Seconds?

I've tried to convert it to datetime2, varchar, digit, decimals and datetime and I either get an error message or a string of digits. I'm looking for something to give me a break down of how many days:hours:minutes:Seconds the time_left is or hours:minutes:Seconds.

sql-server

来源：https://stackoverflow.com/questions/75534168/how-to-convert-a-sql-bignit-column-into-dayshoursminutesseconds

2条答案

按热度按时间

3qpi33ja1#

If they are unix timestamps they are seconds from 1/1/1970.

If so, then you can convert with DATEADD(SECOND, 516542000, '1970-01-01') .

Also refer to this Convert Datetime to Unix timestamp for the reverse process.

赞(0）回复(0）举报 2023-02-28

e7arh2l62#

If this column contains seconds then you can do it as follows :

select time_left,
  CONCAT(
    FLOOR(time_left/(24*3600)),':',
    FLOOR(time_left%(24*3600)/3600),':', 
    FLOOR(time_left%(24*3600)%3600/60),':', 
    (time_left%(24*3600)%3600)%60 
) as "converted"
from mytable;

FLOOR() to return the largest integer value.

Demo here

赞(0）回复(0）举报 2023-02-28

我来回答

How to convert a SQL Bignit column into Days:Hours:Minutes:Seconds

2条答案

相关问题

热门标签

最新问答