/****** Object:  UserDefinedFunction [dbo].[getJavaTimeStampAsDate]    Script Date: 05/26/2010 12:34:34 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[getJavaTimeStampAsDate] (@timeStamp BIGINT) RETURNS DATETIME
AS BEGIN
	/*** GetJavaTimeStampAsDate **************************************************
	*
	* Takes a Java timestamp (a bigint count of miliseconds from 1970-01-01)
	* Returns a datetime
	*
	* Charlie (2010-01-19)
	*
	* Splits the timestamp using integer division into a number of:
	* days, seconds and miliseconds and then adds them one after another
	* to the epoch to achieve the date
	*
	* A SQL INT is a 4 byte signed integer so the maximum number of days will
	* be 2,147,483,647 which is 5,879,489 years. A DATETIME can only hold
	* dates up until 9999-12-31 so we are well covered for scale. Charlie
	*
	*****************************************************************************/

	DECLARE @epoch DATETIME
	DECLARE @return DATETIME
	DECLARE @days INT
	DECLARE @seconds INT

	-- Set the Epoch (standard java epoch is 1st Jan 1970 at 00:00:00.000
	SELECT @epoch = '19700101'

	-- If @timeStamp is NULL return epoch to avoid problems
	IF @timeStamp IS NULL RETURN @epoch

	-- No. of whole days in the timestamp and truncate timestamp
	SELECT @days = @timeStamp / 1000 / 60 / 60 / 24
	SELECT @timeStamp = @timeStamp - CAST(@days AS BIGINT) * 24 * 60 * 60 * 1000

	-- No of whole seconds in the remainder and truncate
	SELECT @seconds = @timeStamp / 1000
	SELECT @timeStamp = @timeStamp - CAST(@seconds AS BIGINT) * 1000

	-- Add days, remainder seconds and finally remainder miliseconds to epoch
	SELECT @return = DATEADD(DAY, @days, @epoch)
	SELECT @return = DATEADD(SECOND, @Seconds, @return)
	SELECT @return = DATEADD(MILLISECOND, @timeStamp, @return)

	-- Return the date
	RETURN @return
END