We covered an introduction to the way Time Matters internally saves dates in a much earlier blog article. Even though I have a date converter (at the bottom of the page here) I wanted to go a bit deeper with this article to include examples of converting dates and times in common applications.

Most of the internal system dates and times in the Time Matters database are stored as an integer. In fact, the only dates and times stored as plain text are user defined date and time fields in the form styles.

There are several technical benefits to using integer based dates and times, primarily date/time math operations as well as an avoidance of the Y2K crisis. We'll skip most of the negatives since we cannot do anything about them (and I like to keep it positive). Let’s focus on how to make something useful and human readable from these integers.

Integer based dates and times are used throughout Time Matters and always on forms with Date and Time fields in area one. User defined areas on forms will never use integer based dates and times, they are plain text and therefore do not require any conversions.

^{Area 1 is an example of integer based dates and times.}

^{User defines areas never use integer based dates and times.}

Understanding Dates

The integer format that Time Matters dates use assumes a value of 1 being equal to 12/29/1800. Knowing this, we can perform conversions in various environments by normalizing the date math as follows:

SQL Server Inline Query Date Formula

CONVERT( VARCHAR(10), DATEADD(day, bil_ [TMIntegerDate] - 4, '1801-01-01'), 126)

This example will return a date converted and formatted as  yyyy-mm-dd. The DATEADD function performs the conversion from an integer to an SQL DateTime format. The CONVERT function allows the date to be displayed in the simple yyyy-mm-dd format. Limiting the VARCHAR to 10 characters allows only the first 10 characters of the resulting DateTime format to be returned.

Excel Date Formula

ExcelDate = [TMIntegerDate] - 3161

For Excel, "Day 1" is 01-01-1900 so the difference between the TM "Day 1" and the Excel "Day 1" is 3,161. The resulting column should be formatted as a date for appropriate viewing in Excel.

Crystal Reports Date Formula Field

DATE(1800,12,28) + {[TMIntegerDate]}

Relatively simple and straightforward, in Crystal Reports simply create a formula field for the date and use the format shown here. The resulting value can be sorted or grouped in native Crystal Reports format.

Understanding Times

Times in Time Matters are very simple to understand and represent the number of "ticks" since midnight. Each tick has a value of 100 per second. Consider the following examples: 100 = 1 second, 6,000 = 1 minute, 360,000 = 1 hour, 8,640,000 = 1 day.

SQL Server Inline Query Time Formula

LTRIM( RIGHT( CONVERT( VARCHAR(20), DATEADD(ms, [TMIntegerTime]* 10, 0),  22), 11) )

This example will return a time converted and formatted as  hh:mm:ss xm. The DATEADD function performs the conversion from an integer to an SQL DateTime format. The CONVERT function allows the time to be displayed in the simple format. In this example the LTRIM and RIGHT functions are used to truncate the date portion of the result.

LEFT( CONVERT (VARCHAR(8),  DATEADD(ms, [TMIntegerTime] * 10, 0), 8), 5)

This example will display the result as hh:mm in 24 hour format.

Excel Time Formula

ExcelTime = ([TMIntegerTime]-1) / 3161

For Excel, time is measured in a fraction of a day and therefore represents the decimal portion of a DateTime formula. The resulting column should be formatted as a time for appropriate viewing in Excel.

Crystal Reports Time Formula Field

TIME( ([TMIntegerTime]-1) / 8640000 )

Similar to Excel, Crystal uses a percentage of a day to derive the time value. In Crystal Reports simply create a formula field for the time and use the format shown here. The resulting value can be sorted or grouped in native Crystal Reports format.