CREATE FUNCTION convertEpoch
(@epochVal bigint) RETURNS datetime
AS BEGIN DECLARE @Return datetime
SELECT @return = dateadd(s,@epochVal/1000,'1970-01-01')
RETURN @return END
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [table field] ) AS ColumnInLocalTime
-- =============================================
-- Author: John Chenault
-- Create date: 10/15/2013
-- Description: Convert a datetime in MS SQL to a
-- float matching the MySQL convention
-- =============================================
CREATE FUNCTION [dbo].[mxfn_ParseDateTimeToFloat] (@inputDateTime DATETIME)
RETURNS FLOAT
AS
BEGIN
DECLARE @ret FLOAT
DECLARE @tempFloat FLOAT
SET @ret = 0
SET @tempFloat = DATEPART(YEAR, @inputDateTime)
SET @tempFloat = @tempFloat * 10000000000
SET @ret = @ret + @tempFloat
SET @tempFloat = DATEPART(MONTH, @inputDateTime)
SET @tempFloat = @tempFloat * 100000000
SET @ret = @ret + @tempFloat
SET @tempFloat = DATEPART(DAY, @inputDateTime)
SET @tempFloat = @tempFloat * 1000000
SET @ret = @ret + @tempFloat
SET @tempFloat = DATEPART(HOUR, @inputDateTime)
SET @tempFloat = @tempFloat * 10000
SET @ret = @ret + @tempFloat
SET @tempFloat = DATEPART(MINUTE, @inputDateTime)
SET @tempFloat = @tempFloat * 100
SET @ret = @ret + @tempFloat
SET @ret = @ret + DATEPART(SECOND, @inputDateTime)
RETURN @ret
END
GO
/****** Object: UserDefinedFunction [dbo].[mxfn_ParseFloatToDateTime] Script Date: 11/22/2019 2:03:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: John Chenault
-- Create date: 10/15/2013
-- Description: Convert a date contained in a MySQL
-- float (i.e. 20131015000000) to a MS SQL datetime
-- =============================================
CREATE FUNCTION [dbo].[mxfn_ParseFloatToDateTime] (@inputFloat FLOAT)
RETURNS DATETIME
AS
BEGIN
DECLARE @floatString NVARCHAR(19)
SET @floatString = STR(@inputFloat, 14)
SET @floatString = SUBSTRING(@floatString, 1, 4) + '/' + SUBSTRING(@floatString, 5, 2) + '/' + SUBSTRING(@floatString, 7, 2) + ' ' + SUBSTRING(@floatString, 9, 2) + ':' + SUBSTRING(@floatString, 11, 2) + ':' + SUBSTRING(@floatString, 13, 2)
RETURN CONVERT(DATETIME, @floatString, 120)
END