SQL Server中的HH:MM格式 [英] HH:MM Format in SQL Server

问题描述

我正在使用存储过程，并且我在其中有一个Name Timespent列，我只想显示为HH:MM格式，该过程运行良好，但是唯一的问题是当前花费的时间为hh:mm :ss格式.指导我如何花时间在HH:MM格式上，并在可能的情况下更正我的程序.该过程是这样的:

I m using a stored procedure and there i m having a column Name timespent which i want to show like HH:MM format only, the procedure is working fine but the only problem is that currently the time spent is coming as hh:mm:ss format. Guide me how i ll get time spent in HH:MM format and correct my procedure if possible. The procedure is like that:

CREATE procedure St_Proc_GetUserReportforCurrentDayTask              
@userID int              
as              
Begin              
    set NoCount on;              
    DECLARE @TODAY DATE                
    SET @TODAY = CONVERT(VARCHAR(10), GETDATE(), 111)              
    select CONVERT(VARCHAR,production.CalendarDate,101) + RIGHT (CONVERT(VARCHAR,production.CalendarDate , 100 ) ,7) as Date,               
    RegionAndProjectInfo.RegionProjectName as Region ,              
    County.CountyName as County,              
    WorkType.WorkTypeName as WorkType,              
    Task.TaskName as Task,  
    Production.VolumeProcessed as 'Volumes Processed',              
    Production.TimeSpent as 'Duration (HH:MM)'              
    from Production               
    inner join RegionAndProjectInfo              
    on              
    RegionAndProjectInfo.RegionProjectID=Production.RegionProjectID              
    inner join County              
    on               
    County.CountyID=Production.CountyID              
    inner join WorkType              
    on              
    WorkType.WorkTypeID=Production.WorkTypeID              
    inner join Task              
    on              
    Task.TaskID=Production.TaskID              
    where Production.UserID=@userID and CalendarDate >= @TODAY              
End 

推荐答案

使用以下代码:

Use the below code:

CREATE procedure St_Proc_GetUserReportforCurrentDayTask              
@userID int              
as              
Begin              
    set NoCount on;              
    DECLARE @TODAY DATE                
    SET @TODAY = CONVERT(VARCHAR(10), GETDATE(), 111)              
    select CONVERT(VARCHAR,production.CalendarDate,101) + RIGHT (CONVERT(VARCHAR,production.CalendarDate , 100 ) ,7) as Date,               
    RegionAndProjectInfo.RegionProjectName as Region ,              
    County.CountyName as County,              
    WorkType.WorkTypeName as WorkType,              
    Task.TaskName as Task,  
    Production.VolumeProcessed as 'Volumes Processed',  
convert(varchar(5),Production.TimeSpent ,108) as 'Duration (HH:MM)'   
    from Production               
    inner join RegionAndProjectInfo              
    on              
    RegionAndProjectInfo.RegionProjectID=Production.RegionProjectID              
    inner join County              
    on               
    County.CountyID=Production.CountyID              
    inner join WorkType              
    on              
    WorkType.WorkTypeID=Production.WorkTypeID              
    inner join Task              
    on              
    Task.TaskID=Production.TaskID              
    where Production.UserID=@userID and CalendarDate >= @TODAY              
End

看这个例子，我已经测试了它的工作原理

look this example i have tested it is working

DECLARE @TODAY DATEtime
    SET @TODAY = CONVERT(VARCHAR(10), GETDATE(), 108)


select CAST
(
(SUM (datepart(hh, convert (varchar, @TODAY, 108))) +
(sum(datepart(mi, convert (varchar, @TODAY, 108)))/60) ) AS VARCHAR(2)
)
+ ':' +
CAST
(
sum(datepart(mi, convert (varchar, @TODAY, 108))) - 60 * (sum(datepart(mi, convert (varchar, @TODAY, 108)))/60)
 as VARCHAR(2))

您可以尝试以下方法:

选择convert(varchar(5)，GETDATE()，108)作为``HH:MM''

谢谢- Girijesh Kumar

You can try this :

select convert(varchar(5),GETDATE(),108) as ''HH:MM''

Thanks - Girijesh Kumar

这篇关于SQL Server中的HH:MM格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！