如何将日期时间值转换为UTC格式 [英] How to convert datetime value to UTC format

问题描述

我需要找到在网站上在线的用户.

为此，我需要将24小时内的lastlogintime转换为UTC时间

然后我需要得到cuurentutctime和lastlogintime之间的差异

如果diff是< 20 mts，用户在线.如何在sql server中完成此操作.

I need to find the users who are online in a website.

For that I need to convert the lastlogintime which is in 24 hrs to UTC time

Then I need to get differnce between the cuurentutctime and the lastlogintime

If diff is < 20 mts, user is online. How is this done in sql server.

SELECT MU.Id, U.UserId, U.UserName, U.FirstName, U.LastName, U.LastLoginTime, 

 önlineStatus =
CASE
When 
DATEDIFF(SECOND,GETUTCDATE(),dateadd(SECOND,datediff(SECOND,getutcdate(),GETDATE()),(CONVERT(varchar(25),U.LastLoginTime,100))))<900 
then 'Online'
ELSE 'Offline' 
END 
FROM MembershipUser MU INNER JOIN User U
ON MU.UserId = U.UserId

我尝试了这个.但是问题是getdate()的格式为12小时，而LastLoginTime的格式为24小时.

任何人都可以帮忙.我不知道如何使用相同的格式制作这些文件

I tried with this. But the issue is getdate() is in 12 hours format and LastLoginTime is in 24 hours.

Can anyone pls help. I don''t know how to make these in same format

推荐答案

尝试使用此代码将DateTime 转换为UTC DateTime.

Hi,
Try this code to convert the DateTime to UTC DateTime.

DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()

-- convert local date to utc date
DECLARE @UTCDate DATETIME
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @LocalDate)

SELECT @LocalDate, @UTCDate

现在，使用DATEDIFF 函数来计算时间并获取解决方案.


希望它对您有用.
--Amit

Now find the use the DATEDIFF function to calculate the time and get your solution.


Hope it works for you.
--Amit

您的意思是这样的
选择DATEDIFF(SECOND，@ LocalDate，@ UTCDate)

如果您将您的逻辑整合到我的查询中，这将非常有帮助
即

You mean like this
select DATEDIFF(SECOND,@LocalDate,@UTCDate)

It would be really helpful if you pls incoporate your logic to my query
ie

SELECT MU.Id, U.UserId, U.UserName, U.FirstName, U.LastName, U.LastLoginTime,

 önlineStatus =
CASE
When
DATEDIFF(SECOND,GETUTCDATE(),dateadd(SECOND,datediff(SECOND,getutcdate(),GETDATE()),(CONVERT(varchar(25),U.LastLoginTime,100))))<900
then 'Online'
ELSE 'Offline'
END
FROM MembershipUser MU INNER JOIN User U
ON MU.UserId = U.UserId

您能否告诉我如何在查询中包括此内容.此外，您已将localtime设置为getdate().我的datecolumn是24小时格式，但是getdate()是12小时格式.

Hi,
Can you pls tell how I can include this in my query.Also, you have set localtime to getdate(). My datecolumn is in 24 hour format, but getdate() is in 12 hour format.

这篇关于如何将日期时间值转换为UTC格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！