I recently encountered an interesting question on the forums the other day. The question was how to determine the average time taken by a single execution of the report provided we know how many times the report ran and the total time taken for all those executions.

The challenge is that the total time taken for all the report executions is a timespan value (datatype TIME in SQL Server). A TIME value cannot be divided by an INTEGER. If we try to do that, we run into an error – an operand clash.

USE [tempdb];
GO
DECLARE @timeSpan TIME = '03:18:20';
DECLARE @numberOfExecutions INT = 99;

SELECT @timeSpan/@numberOfExecutions;
GO

Msg 206, Level 16, State 2, Line 6
Operand type clash: time is incompatible with int

The solution is to realize that a timespan/TIME value is ultimately the number of seconds passed from a given instant. Once the timespan is converted to the appropriate unit (number of seconds), dividing by the number of executions should be quite simple.

Here’s the working example:

USE [tempdb];
GO
DECLARE @timeSpan TIME = '03:18:20';
DECLARE @numberOfExecutions INT = 99;

SELECT @timeSpan AS TotalActiveTime,
       DATEDIFF(SECOND,'1900-01-01 00:00:00.000',CAST(@timeSpan AS DATETIME)) AS TotalExecutionTimeInSeconds,
       DATEDIFF(SECOND,'1900-01-01 00:00:00.000',CAST(@timeSpan AS DATETIME))/(@numberOfExecutions * 1.0) AS TimePerExecution;
GO

/* RESULTS
TotalActiveTime  TotalExecutionTimeInSeconds TimePerExecution   
---------------- --------------------------- -------------------
03:18:20.0000000 11900                       120.20202020202020
*/

I trust this simple thought will help in resolving a business problem someday.

Until we meet next time,

Be courteous. Drive responsibly.

Helping the community via forums often leads to some very interesting moments. Recently, I came across quite a common question – as part of a data migration, someone wanted to pad integers with zeroes. There are various variations to this question, namely:

How do I pad zeroes to  convert an integer to a fixed length string?

How do I pad zeroes before an integer?

How to I pad blank spaces before an integer?

All of these questions have quite a simple solution, which I am going to present before you today.

The script demonstrates the process of padding the required values to a set of integers in a test table. The script:

1. Converts the Integer to a string
2. Appends this string representation of the integer to the padding string
3. Finally, returns the required number of characters from the right of the string

For the purposes of this demo, I have shown the result with two padding characters – a zero (0) and an asterisk (*).

Have you ever faced such a requirement as part of a data migration or an integration? Do you use a similar approach? Do share your thoughts and suggestions in the space below.

--Pad zeroes in string representation of a number
USE tempdb;
GO
--Safety Check
IF OBJECT_ID('dbo.TestTable','U') IS NOT NULL
BEGIN
   DROP TABLE dbo.TestTable;
END
GO

--Create the test tables
CREATE TABLE dbo.TestTable
            (RecordId    INT NOT NULL IDENTITY(1,1),
             RecordValue INT     NULL
            );
GO

--Populate some test data
INSERT INTO dbo.TestTable (RecordValue)
VALUES (123),
       (1023),
       (NULL);
GO

/**************** PADDING CHARACTER: ZERO (0) ****************************/

--Change the padding character and the number of strings as required
DECLARE @requiredStringLength INT = 10;
DECLARE @paddingCharacter CHAR(1) = '0'

--The script:
--1. Converts the Integer to a string
--2. Appends this string representation of the integer to the padding string
--3. Finally, returns the required number of characters from the right of the string
SELECT RecordId,
       RecordValue AS OriginalValue,
       RIGHT( (REPLICATE( @paddingCharacter, @requiredStringLength )
              + CAST(RecordValue AS VARCHAR(20))
              ),
              @requiredStringLength
            ) AS PaddedValue
FROM dbo.TestTable AS tt;
GO

/* RESULTS
RecordId    OriginalValue PaddedValue
----------- ------------- ------------
1           123           0000000123
2           1023          0000001023
3           NULL          NULL

*/

/**************** PADDING CHARACTER: ASTERISK (*) ****************************/

--Change the padding character and the number of strings as required
DECLARE @requiredStringLength INT = 10;
DECLARE @paddingCharacter CHAR(1) = '*'

--The script:
--1. Converts the Integer to a string
--2. Appends this string representation of the integer to the padding string
--3. Finally, returns the required number of characters from the right of the string
SELECT RecordId,
       RecordValue AS OriginalValue,
       RIGHT( (REPLICATE( @paddingCharacter, @requiredStringLength )
               + CAST(RecordValue AS VARCHAR(20))
              ),
              @requiredStringLength
            ) AS PaddedValue
FROM dbo.TestTable AS tt;
GO

/* RESULTS
RecordId    OriginalValue PaddedValue
----------- ------------- ------------
1           123           *******123
2           1023          ******1023
3           NULL          NULL
*/

Until we meet next time,

Be courteous. Drive responsibly.