epoch time to datetime

CREATE FUNCTION convertEpoch

(@epochVal bigint) RETURNS datetime

AS BEGIN DECLARE @Return datetime

SELECT @return = dateadd(s,@epochVal/1000,'1970-01-01')

RETURN @return END

Datetime to epoch time

SELECT DATEDIFF(s, '1970-01-01 00:00:00', [dtLastModified])  

Convert UTC Time to Local Time

DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [table field] )  AS ColumnInLocalTime



MSSQL to MySQL and back


-- =============================================

-- 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