Home | Converting “DateTime” Field in BarTender Database to Regular Date and Time – Instructions
Print or Email this article

Converting “DateTime” Field in BarTender Database to Regular Date and Time – Instructions

Target Audience: Advanced User, IT Manager
Article #: 10810

Summary

When BarTender is installed with Microsoft SQL database, the datetime field (DTF) in the SQL table will be set for the BIGINT data type. This allows the field to contain an integer (number) with over a dozen digits. This can be quite cumbersome when put into use, so many users prefer to convert the field to a different format. This article provides instructions on converting the DateTime field in BarTender Database to regular date and time format.

Information

The number stored in the DateTime field in SQL is a very large number. We have seen it as large as 18 digits. Apparently, it represents a time from January, 01, 0001 as a value of microsecond/10 (1/10,000,000 second). For the purpose of this article we will call this unit “VB” (very big).

So, for a non-Math Genius, mortal human, how do we translate this into a format that we can understand? Obviously, we need to shrink the number down to a more manageable size.

Let’s start with:

599,266,080,000,000,000This is the number of VB from January 01, 0001 to January 01, 1900.  You’ll just have to take my word for it.

A DTF value of 599,266,080,000,000,000) / 600,000,000 returns the number of minutes since 1/1/1990.

Now, you’ll need to convert the result into a readable format using SQL query:

DateAdd (minutes, (DTF – 599,266,080,000,000,000) / 600,000,000, cast(1900-01-01′ as DateTime)) returns the date and time in YYY-MM-DD HH:MM:SS.sss format.

EXAMPLE

DateAdd (minutes, (636891043257090000 – 599266080000000000) / 600000000), cast(‘1900-01-01’ as DateTime))  returns 2019-03-25 09:52:00.000

You’ll note that we have narrowed the accuracy down to the minute level. If you wish to refine the accuracy to seconds, you will need to modify the formula as the function DateAdd cannot accept the BigInt data type. The value of (DTF – 599,266,080,000,000,000) must be less than 2,147,483,647 (2^31). That is the limitation of INT data type.

You can try to increase the value 599,266,080,000,000,000 to bring the offset date later than 1900-01-01. For example, you can bring the offset date to 1990-01-01 (you have to figure out the number yourself). The result of (DTF-xxxxxx) may be small enough that it confirms to the INT data type (2^31).

Outside sources and helpful links

StackOverflow Topic

Author

Dave Klement

Was this article helpful?

Related Articles