Integer to Date/Time

Cory Fifield -

Trouble viewing images? Click on images to enlarge.

 

In this article we cover a few methods to get a time value out of date/time data stored as an integer. If you would like to download the sample view used in the article, click here. 

 

The methods below are going to take data that is stored as an integer for seconds.

Example: 600 = 10 minutes or 3600 = 1 Hour

 

Finished Reports Method:

1. Add a label into Finished Reports.

mceclip0.png

 

2. Click the Label Tasks drop down. (Select the label and click the blue arrow)

mceclip3.png

 

3. Open the expression editor and paste the following expression.

 

FormatString('{0:00}:{1:00}:{2:00}', Floor(ToDecimal([Column_0])/3600), Floor((ToDecimal([Column_0])-3600*Floor(ToDecimal([Column_0])/(3600)))/(60)), ToDecimal([Column_0])%60)

 

mceclip4.png

 

 

4. Replace all instances of [YourField] with the field you want to use then click OK.

 

 

5. Preview the Finished Report.

mceclip6.png

 

 

Alternate Finished Reports Method (Recommended):

1. Create a new calculated field. (Right click in Field List and select Add Calculated Field)

mceclip8.png

 

 

2. Set the Data Source, and Field Type.

mceclip7.png

 

 

3. Open the expression editor and paste the following expression.

addseconds('1/1/1900', [YourField])

 

mceclip9.png

 

 

4. Replace [YourField] in the expression with the field you are using.

 

 

5. Drag the field into FR and format the expression to {0:HH:mm:ss}

mceclip10.png

 

 

Visualize Method:

1. Create A Calculated Field

mceclip12.png

 

2. Paste the following expression into the expression editor.

Iif(
ToInt([YourField] % (24 * 60 * 60) / (60 * 60)) < 10, '0' +
ToStr([YourField] % (24 * 60 * 60) / (60 * 60)),
ToStr([YourField] % (24 * 60 * 60) / (60 * 60)))
+ ':' +
Iif(
ToInt([YourField] % (60 * 60) / 60) < 10, '0' +
ToStr([YourField] % (60 * 60) / 60), ToStr([YourField] % (60 * 60) / 60))
+ ':' +
Iif(
ToInt([YourField] % 60) < 10, '0' + ToStr([YourField] % 60) , ToStr([YourField] % 60))

 

mceclip13.png

 

3. Replace [YourField] with the field you are using.

 

4. Drag the field into your Visualize fields and preview the results.

mceclip14.png

 

 

Have more questions? Submit a request

Comments