Convert from or to Java time / SQL SERVER
Par Draggi le mardi, février 11 2014, 13:33 - SQL - Lien permanent
I recently had to create a UDF that would convert a Java time value to a datetime value. We have an application that stores dates and times as the number of milliseconds since Jan. 1, 1970. Well that isn't very pleasant to the human eye when trying to debug things.
So, here is a function to convert it to datetime:
CREATE FUNCTION udf_ConvertJavaTimeToDateTime
(@milliseconds BIGINT)
RETURNS DATETIME
AS
BEGIN
DECLARE @DateTime DATETIME
SELECT @DateTime = DATEADD(s, @milliseconds/1000, '1970-01-01 00:00:00.000' )
RETURN @DateTime
END
And here is a function to convert a datetime value to Java time:
CREATE FUNCTION udf_ConvertDateTimeToMSSince1970
(@DateTime DATETIME)
RETURNS BIGINT
AS
BEGIN
DECLARE @seconds BIGINT
SELECT @seconds = DATEDIFF(s, '1970-01-01 00:00:00.000', @DateTime)
RETURN @seconds * 1000 --converts it to milliseconds
END