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