CODEWITHSUNDEEP
sqlsql-servertype-conversion
Pat
Pat

Reputation: 668

SQL Converting int to varchar

I need help converting an integer to a varchar.

I'm trying to write a procedure that takes in a ProfileID and a Currenttime; using those two values it finds the start time of the profileID and subtracts currenttime from starttime and returns hours:minutes:seconds.

What am I doing wrong, is there a better way to write this?

Thanks. 

CREATE PROCEDURE [dbo].[CalculateElaspedTime]
    -- Add the parameters for the stored procedure here
    @ProfileID nvarchar(10),
    @CurrentDateTime datetime = '' 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    if @CurrentDateTime = CAST('' as datetime)
    set @CurrentDateTime = GETDATE()

    DECLARE @StartTime datetime;
    DECLARE @ElaspedTime time;
    DECLARE @hh int;
    DECLARE @mm int;
    DECLARE @ss int;
    Declare @TimeString varchar
    set @StartTime = (Select top 1 [DateTime] From Log WHERE ProfileID = @ProfileID);
    set @hh = DateDiff(hour,@StartTime,@CurrentDateTime);
    set @mm = DateDiff(minute,@StartTime,@CurrentDateTime)-60*@hh;
    set @ss = DateDiff(second,@StartTime,@CurrentDateTime)-60*@mm;

    set @TimeString = (Select CAST(@hh as varchar)); -- Fails Here 
    set @ElaspedTime = convert(datetime, cast(@hh as varchar) + ':' + cast(@mm as varchar) + ':' + cast(@ss as varchar));
    INSERT INTO Log (ElaspedTime) Values (@ElaspedTime);
END

Upvotes: 3

Views: 39566

Answers (2)

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

Try this. All of that excitement in the function may be unnecessary.

CONVERT(varchar(10),(@CurrentDateTime-@Start_Time),108)

Upvotes: 8

Nicholas Carey
Nicholas Carey

Reputation: 74187

One problem you have is this statement:

set @StartTime = (Select top 1 [DateTime] From Log WHERE ProfileID = @ProfileID);

Its results are indeterminate as SQL makes no guarantees about result order unless you explicitly specify it in an ORDER BY clause. You should be using ORDER BY or using an aggregate function like MAX() to get the row you want.

And you're doing a lot more work than necessary. SQL Server (recent versions, anyway) support date arithmetic, with the result of subtracting two dates being another date (offset from the SQL Server epoch of 1 Jan 1900 00:00:00.000. This simpler form ought to do you, unless the elapsed time will exceed 1 day:

create procedure dbo.CalculateElaspedTime

  @ProfileID       nvarchar(10)  ,
  @CurrentDateTime datetime = '' 

as

  set nocount on

  declare
    @now     dateTime    ,
    @start   datetime    ,
    @elapsed varchar(32)

  select @now = case coalesce(@currentDateTime,'') when '' then current_timestamp else @currentDateTime end ,
         @start = max( [DateTime] )
  from dbo.Log
  where ProfileId = @profileId

  set @elapsed = convert(varchar,@now-@start,108)

  insert dbo.Log ( ElapsedTime ) Values (@elapsed);

  return 0
go

If your elapsed time might exceed one day, then your original approach is what you want:

create procedure dbo.CalculateElaspedTime

  @ProfileID       nvarchar(10)  ,
  @CurrentDateTime datetime = '' 

as

  set nocount on

  declare @now     dateTime = case coalesce(@currentDateTime,'') when '' then current_timestamp else @currentDateTime end ,
  declare @start   datetime = ( select max([DateTime]) from dbo.Log where profileId = @profileId )
  declare @elapsed int      = select datediff(ss,@now,@start)
  declare
    @hh int ,
    @mm int ,
    @ss int

  set @hh      = @elapsed / 3600 -- 3600 is seconds/hour
  set @elapsed = @elapsed % 3600 
  set @mm      = @elapsed / 60   -- 60 is seconds/minute
  set @elapsed = @elapsed % 60
  set @ss      = @elapsed / 1   -- 1 is seconds/second :)

  declare @hhmmss =       right('00'+convert(varchar,@hh),2)
                  + ':' + right('00'+convert(varchar,@mm),2)
                  + ':' + right('00'+convert(varchar,@ss),2)

  insert dbo.Log ( ElapsedTime ) Values (@hhmmss);

  return 0
go

Upvotes: 1

Related Questions