UTC to Unix Time Stamp In SQL server

     The below function return UTC time and Unix Time Stamp in sql, based on country offset value and local time.

What is the unix time stamp?

     The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970 at UTC. Therefore, the unix time stamp is merely the number of seconds between a particular date and the Unix Epoch. It is very help full to handle  date time in client side.

CREATE FUNCTION dbo.fnGetUtcTimeStamp_Sample(@LocalDate DATETIME,@CountryOffset VARCHAR(10))

RETURNS @TempTable TABLE(LocalDate DATETIME,UnixTimeStamp VARCHAR(50),UTCDate DATETIME)

AS

/*

Return UTC Time, Unix Time Stamp Based On Local DateTime and Country offset

*/

BEGIN

   DECLARE @UTCtime VARCHAR(50)

   DECLARE @MinOfset INT

   DECLARE @UtcDate DATETIME

   SET @MinOfset = PATINDEX('%.%', @CountryOffset)

   SET @MinOfset = CAST(SUBSTRING(@CountryOffset,@MinOfset+1, 5) AS FLOAT)

   IF(LEFT(@CountryOffset,1) = '+' )

     BEGIN

       SET @UtcDate = DATEADD(HOUR,-CAST(SUBSTRING(@CountryOffset,2, 5) AS FLOAT),@LocalDate)

       IF (@MinOfset>0)

          SET @UtcDate = DATEADD(MINUTE,-@MinOfset,@UtcDate)

     END

  ELSE

     BEGIN

       SET @UtcDate = DATEADD(HOUR,CAST(SUBSTRING(@CountryOffset,2, 5) AS FLOAT),@LocalDate)

       IF (@MinOfset>0)

          SET @UtcDate = DATEADD(MINUTE,@MinOfset,@UtcDate)

    END

  SET @UTCtime = CAST(DATEDIFF(SECOND,{d '1970-01-01'},@UtcDate) AS VARCHAR(50))

   INSERT INTO @TempTable(LocalDate,UnixTimeStamp,UTCDate)

  SELECT @LocalDate,@UTCtime, @UtcDate

  RETURN

END

/*Function Execution*/

SELECT * FROM dbo.fnGetUtcTimeStamp_Sample('2018-06-05 16:00:00','+5.30')

  What happens on January 19, 2038?

 

         On this date the Unix Time Stamp will Not work due to a 32-bit overflow. Before this moment millions of applications will need to either adopt a new convention for time stamps or be migrated to 64-bit systems.