CODEWITHSUNDEEP
sqlsql-server-2008
Dharmendra Kumar Singh
Dharmendra Kumar Singh

Reputation: 3001

HH:MM:SS:Msec to HH:MM:SS in stored procedure

I have a stored procedure which update a table based on such calculation and the calculation is done as column name (Calendatedate) - (Current System Date Time) and update this information to a column (TimeSpent) and display the value in Hh:Mm:SS:Msec format.

The query is working fine but I want to update it in such a way so that the time spent should be only HH:MM:SS format. Please help me that how I remove that Msec from the time spent.

CREATE procedure St_Proc_UpdateTimeSpent        
@timeEntryID int,        
@status int output        
as begin         
set nocount on;        
declare @Date dateTime;        
set @Date=GETDATE(); 
    update Production set TimeSpent=(SELECT CONVERT(VARCHAR(20),DateAdd(SS,Datediff(ss,CalendarDate, @Date)%(60*60*24),0),114)),  
    IsTaskCompleted=1       
    where productionTimeEntryID=@timeEntryID         
    set @status=1;        
    return @status;        
end

Upvotes: 2

Views: 510

Answers (1)

marc_s
marc_s

Reputation: 755043

You can just use style 108 instead of 114 in the CONVERT function to get only the hh:mm:ss:

CREATE PROCEDURE dbo.St_Proc_UpdateTimeSpent        
     @timeEntryID int,        
     @status int output        
AS BEGIN
   SET NOCOUNT ON;

   DECLARE @Date DATETIME;
   SET @Date = GETDATE(); 

   UPDATE dbo.Production 
   SET TimeSpent = CONVERT(VARCHAR(20), DATEADD(SS, DATEDIFF(ss, CalendarDate, @Date)%(60*60*24),0), 108),  
       IsTaskCompleted = 1
   WHERE  
       productionTimeEntryID = @timeEntryID         

   SET @status = 1;

   RETURN @status;        
END

See the excellent MSDN documentation on CAST and CONVERT for a comprehensive list of all supported styles when converting DATETIME to VARCHAR (and back)

BTW: SQL Server 2008 also introduced a TIME datatype which would probably be a better fit than a VARCHAR to store your TimeSpent values ... check it out!

Upvotes: 2

Related Questions