/********************************************** timestamp conversion (seconds) **********************************************/--normal time conversion to timestamp (seconds)SELECT DATEDIFF(SS,'1970-1-1 00:00:00',GETDATE()) --millisecond timestamp to normal time (seconds)SELECT DATEADD(SS,1447842393,'1970-1-1 00:00:00')/********************************************** Timestamp conversion (ms) **********************************************/--normal time conversion to timestamp (MS)DECLARE @ADATE DATETIMESET @ADATE = GETDATE()SELECT CONVERT(BIGINT,DATEDIFF(MI,'1970-01-01 00:00:00.000',@ADATE))* 60000 + DATEPARTS@ADATE)* + + DATEPART(MS,@ADATE)--millisecond timestamp to normal time (ms)DECLARE @UNIXTIME BIGINTSET @UNIXTIME = 1447842306000SELECT DATEADD(MS,@UNIXTIME % 60000,DATEADD(MI,@UNIXTIME / 60000,'1970-01-01 00:00:00.000'))
SQL Server timestamp vs. normal format conversion