dimanche 28 juin 2015

How can compare between times in while loop in stored procedure?

Please help me on this, I have been working to do conditions to get the time within the 24 hours only.

So I have something like this. Can anyone help me or tell me if the conditions is correct.

my @DisplayStartTime = 11:00PM

Basically my @nextStartTime = 11:00PM

OPEN cPhosLineTimeSlot

FETCH NEXT FROM cPhosLineTimeSlot
      INTO @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance 

WHILE @@FETCH_STATUS = 0
BEGIN
    Declare @nextStartTime nvarchar(30)
    set @nextStartTime = @DisplayStartTime

    --insert into #ActualTimeSlot
    --select @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance 

    WHILE (convert(varchar, convert(time, @nextStartTime), 100) < DATEADD(day, -1, GETDATE()))
    BEGIN
        SET @DisplayStartTime = @nextStartTime

        SELECT @nextStartTime = ltrim(right(convert(nvarchar(100), DATEADD(minute, @Frequency, @nextStartTime)), 8))

        INSERT INTO #ActualTimeSlot
            SELECT @Parameter, @DisplayStartTime, 
                   @nextStartTime, @CodeEndTime, @Frequency, @Tolerance 


        SET @intFlag +=1
    END 

    SET @intFlag = 1

    FETCH NEXT FROM cPhosLineTimeSlot
        INTO @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance 
END

CLOSE cPhosLineTimeSlot
DEALLOCATE cPhosLineTimeSlot

This is the sample:

enter image description here

Aucun commentaire:

Enregistrer un commentaire