CREATE 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

quote:

---

Hi

Any other type to convert

sql2020

---

SELECT DATEADD(SECOND, 1120073114, '19700101')

SELECT
    DATEADD(SECOND, <yourColumn>, '19700101') AS [converted DateTime]
FROM
    <theTable>

quote:

---

Originally posted by Transact Charlie

If it's like a java timestamp (a count of milliseconds from a set time) then this is the function I wrote to do the job.

CREATE 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

If that's not what you need (to convert milisecond count from an epoch startdate to a DATETIME) then give some sample data and required output.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

create function [dbo].[F_JAVATIME_TO_DATETIME]
	( @JAVATIME bigint )
returns  datetime
as
/*
Function: F_JAVATIME_TO_DATETIME

	Finds SQL Server datetime from JAVA time.
	JAVA time is the difference in milliseconds from
	1970-01-01 00:00:00.000 UDT.

	Valid @JAVATIME range is -6847804800001 through 253402300799998.
	Return null if @JAVATIME is outside this range.
*/
begin

if @JAVATIME between -6847804800001 and 253402300799998
	begin 
	return 	dateadd(ms,@JAVATIME%86400000,(@JAVATIME/86400000)+25567)
	end

return null

end

quote:

---

Originally posted by sql2020

Actually problem is time convertion

this is Epoch time: 1262887606

when i use "DATEADD(SECOND, 1120073114, '1970-01-01 00:00:00')" this it display time is : 2010-01-07 18:06:46.000

Actually i want this time : 2010-01-07 13:06:46


where i modify for this result. please tell me.




sql2020

---

quote:

---

Originally posted by sql2020

Hi this i got result 2010-01-07 18:06:46.000 but i want 2010-01-07 18:06:46 how to remove last 3 zeros


sql2020

---