靜默之地

--輸入秒數 顯示 HH:MM:SS

DECLARE @SEC int
Set @SEC='4018'

DECLARE @HHMMSS VARCHAR(10)
--使用DATEADD 這個功能把秒轉成時間格式之後
--再用Convert Varchar的功能轉日期的格式為108 hh:mi:ss
SET @HHMMSS = CONVERT(varchar, DATEADD(s, @SEC, 0), 108)

-- 結果
select @HHMMSS

--輸入HH:MM:SS 顯示 秒數

DECLARE @time time(4)
set @time='01:06:58'

DECLARE @SEC int
--使用DATEDIFF 計算出秒數
set @SEC=DATEDIFF(s,0,@time)

-- 結果
select @SEC

判斷開機到現在多少  天 時 分 秒

declare @enddate datetime
declare @Startdate datetime

select @Startdate=getdate(),@enddate=sqlserver_start_time FROM sys.dm_os_sys_info
DECLARE @SEC int

select @SEC=datediff(SS, @enddate,@Startdate)
select @SEC

declare @ss int
declare @mm int
declare @hh int
declare @date int

select @date=@SEC/60/60/24, @hh=@SEC/60/60%24, @SS=@SEC%60 , @mm=@SEC/60%60
select @date '天',@hh '時',@mm '分',@ss '秒'

SELECT replace(@@servername,'\'+@@SERVICENAME,'') SERVICENAME,@@SERVICENAME SERVICENAME,getdate() ExecTime,sqlserver_start_time
,convert(varchar,datediff(dd,sqlserver_start_time,getdate()))+N'天'+Left(CONVERT(VARCHAR, DATEADD(s, datediff(ss,sqlserver_start_time,getdate()), 0), 108),2)+N'時'+SUBSTRING(CONVERT(VARCHAR, DATEADD(s, datediff(ss,sqlserver_start_time,getdate()), 0), 108),4,2)+N'分'+RIGHT(CONVERT(VARCHAR, DATEADD(s, datediff(ss,sqlserver_start_time,getdate()), 0), 108),2)+N'秒' StartTimes
FROM sys.dm_os_sys_info