Converting Openfire IM datetime values in SQL Server to / from VARCHAR(15) and DATETIME data types
- by Brian Biales
A client is using Openfire IM for their users, and would like some custom queries to audit user conversations (which are stored by Openfire in tables in the SQL Server database).
Because Openfire supports multiple database servers and multiple platforms, the designers chose to store all date/time stamps in the database as 15 character strings, which get converted to Java Date objects in their code (Openfire is written in Java).
I did some digging around, and, so I don't forget and in case someone else will find this useful, I will put the simple algorithms here for converting back and forth between SQL DATETIME and the Java string representation.
The Java string representation is the number of milliseconds since 1/1/1970.
SQL Server's DATETIME is actually represented as a float, the value being the number of days since 1/1/1900, the portion after the decimal point representing the hours/minutes/seconds/milliseconds... as a fractional part of a day.
Try this and you will see this is true:
SELECT CAST(0 AS DATETIME)
and you will see it returns the date 1/1/1900.
The difference in days between SQL Server's 0 date of 1/1/1900 and the Java representation's 0 date of 1/1/1970 is found easily using the following SQL:
SELECT DATEDIFF(D, '1900-01-01', '1970-01-01')
which returns 25567. There are 25567 days between these dates.
So to convert from the Java string to SQL Server's date time, we need to convert the number of milliseconds to a floating point representation of the number of days since 1/1/1970, then add the 25567 to change this to the number of days since 1/1/1900. To convert to days, you need to divide the number by 1000 ms/s, then by 60 seconds/minute, then by 60 minutes/hour, then by 24 hours/day. Or simply divide by 1000*60*60*24, or 86400000. So, to summarize, we need to cast this string as a float, divide by 86400000 milliseconds/day, then add 25567 days, and cast the resulting value to a DateTime. Here is an example:
DECLARE @tmp as VARCHAR(15)
SET @tmp = '1268231722123'
SELECT @tmp as JavaTime, CAST((CAST(@tmp AS FLOAT) / 86400000) + 25567 AS DATETIME) as SQLTime
To convert from SQL datetime back to the Java time format is not quite as simple, I found, because floats of that size do not convert nicely to strings, they end up in scientific notation using the CONVERT function or CAST function. But I found a couple ways around that problem. You can convert a date to the number of seconds since 1/1/1970 very easily using the DATEDIFF function, as this value fits in an Int. If you don't need to worry about the milliseconds, simply cast this integer as a string, and then concatenate '000' at the end, essentially multiplying this number by 1000, and making it milliseconds since 1/1/1970. If, however, you do care about the milliseconds, you will need to use DATEPART to get the milliseconds part of the date, cast this integer to a string, and then pad zeros on the left to make sure this is three digits, and concatenate these three digits to the number of seconds string above. And finally, I discovered by casting to DECIMAL(15,0) then to VARCHAR(15), I avoid the scientific notation issue. So here are all my examples, pick the one you like best...
First, here is the simple approach if you don't care about the milliseconds:
DECLARE @tmp as VARCHAR(15)
DECLARE @dt as DATETIME
SET @dt = '2010-03-10 14:35:22.123'
SET @tmp = CAST(DATEDIFF(s, '1970-01-01 00:00:00' , @dt) AS VARCHAR(15)) + '000'
SELECT @tmp as JavaTime, @dt as SQLTime
If you want to keep the milliseconds:
DECLARE @tmp as VARCHAR(15)
DECLARE @dt as DATETIME
DECLARE @ms as int
SET @dt = '2010-03-10 14:35:22.123'
SET @ms as DATEPART(ms, @dt)
SET @tmp = CAST(DATEDIFF(s, '1970-01-01 00:00:00' , @dt) AS VARCHAR(15))
+ RIGHT('000' + CAST(@ms AS VARCHAR(3)), 3)
SELECT @tmp as JavaTime, @dt as SQLTime
Or, in one fell swoop:
DECLARE @dt as DATETIME
SET @dt = '2010-03-10 14:35:22.123'
SELECT @dt as SQLTime
, CAST(DATEDIFF(s, '1970-01-01 00:00:00' , @dt) AS VARCHAR(15))
+ RIGHT('000' + CAST( DATEPART(ms, @dt) AS VARCHAR(3)), 3) as JavaTime
And finally, a way to simply reverse the math used converting from Java date to SQL date.
Note the parenthesis - watch out for operator precedence, you want to subtract, then multiply:
DECLARE @dt as DATETIME
SET @dt = '2010-03-10 14:35:22.123'
SELECT @dt as SQLTime
, CAST(CAST((CAST(@dt as Float) - 25567.0) * 86400000.0 as DECIMAL(15,0)) as VARCHAR(15)) as JavaTime
Interestingly, I found that converting to SQL Date time can lose some accuracy, when I converted the time above to Java time then converted that back to DateTime, the number of milliseconds is 120, not 123. As I am not interested in the milliseconds, this is ok for me. But you may want to look into using DateTime2 in SQL Server 2008 for more accuracy.
