0

I have a date in MySQL: 1395774998. I understand this date is a number of milliseconds.

I used epochconverter.com to convert this date to:

**GMT: Tue, 25 Mar 2014 19:16:38 GMT**

I would like to convert this MySQL date to SQL Server without losing information.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Gauttam Jada
  • 588
  • 4
  • 7
  • You wan to convert the date to milliseonds or vice versa in Sql server? – Rahul Tripathi Jun 23 '15 at 08:48
  • Let say simple example, today date in sql server millisecond format 1395774998 like this, please look into that example given number is specific to today's date. – Gauttam Jada Jun 23 '15 at 08:52
  • I think the date is actually in seconds (1395774998 is around 44.25 years - and base date is 1/1/1970) - AFAIK MySQL TimeStamp does not support milliseconds (see also http://stackoverflow.com/questions/2572209/why-doesnt-mysql-support-millisecond-microsecond-precision) – PaulF Jun 23 '15 at 09:00
  • I'm not sure, but I need solution in SQL server, due to DB Migration – Gauttam Jada Jun 23 '15 at 09:01
  • see http://stackoverflow.com/questions/9031160/convert-seconds-to-datetime-in-sql-server – PaulF Jun 23 '15 at 09:04
  • @PaulF But according to my assumption, that is the diffrence between '01-01-1970' to till date in milliseconds, I may need to go with DATEDIFF function to find out exact millisecond. – Gauttam Jada Jun 23 '15 at 09:22
  • (((1395774998 / 3600) / 24) / 365) = 44.25 (first div is to hours, next to days, next to approximate years) 1395774998 in milliseconds is only 387.7 hours or just over 16 days – PaulF Jun 23 '15 at 09:28
  • But if you need milliseconds for sql-server multiply by 1000 - MySQL is only accurate to seconds – PaulF Jun 23 '15 at 09:31
  • I hope I understand the original question - I made an honest attempt to edit and maintain the original intent. – Upperstage Jun 23 '15 at 15:06
  • What is the column's type and how are you doing the migration? If it is a date type you wouldn't have to do such conversions - your MySQL provider would read it as a date/time and SQL Server's provider would store it as a datetime or datetime2 (whatever the target column's type is) – Panagiotis Kanavos Jun 23 '15 at 15:13

1 Answers1

0

Please try this

DECLARE @Datetime BIGINT, @Result datetime, @LocalDatetime  BIGINT;

SET @Datetime = 1395774998
SET @LocalDatetime = @Datetime - DATEDIFF(second,GETDATE(),GETUTCDATE())
SET @Result = (SELECT DATEADD(second,@LocalDatetime , CAST('1970-01-01 00:00:00' AS datetime)))

select @Result

OR function

CREATE FUNCTION dbo.ufn_ConvertUnixTimestampToDateTime (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @LocalDatetime BIGINT
    SET @LocalDatetime = @Datetime - DATEDIFF(second,GETDATE(),GETUTCDATE())
    RETURN (SELECT DATEADD(second,@LocalDatetime, CAST('1970-01-01 00:00:00' AS datetime)))
END;
Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116