Sql 时间相互转换~

当前时间转UTC时间：

DECLARE @current_datetime DATETIME;
DECLARE @days_from_0001_to_1900 BIGINT;
DECLARE @ticks_per_millisecond BIGINT;
DECLARE @ticks_per_day BIGINT;
DECLARE @millisecond_of_time BIGINT;
DECLARE @ticks_of_days BIGINT;
DECLARE @LocalDate DATETIME,
@UTCDate DATETIME,
@LocalDate2 DATETIME
 
SET @LocalDate = GETDATE()
SET @UTCDate = DATEADD(hour, DATEDIFF(hour,GETDATE(),GETUTCDATE()), @LocalDate)
SET @LocalDate2 = DATEADD(hour, DATEDIFF(hour,GETUTCDATE(),GETDATE()), @UTCDate)
 
SELECT '1. Now' [Now], @LocalDate [DateTime]
UNION
SELECT '2. UTC', @UTCDate
UNION
SELECT '3. Now again', @LocalDate2

当前时间转Ticks,Ticks转当前时间

DECLARE @ticks_of_time BIGINT;
DECLARE @ticks BIGINT;
SET @current_datetime = GETUTCDATE();
SET @days_from_0001_to_1900 = 693595;
SET @ticks_per_millisecond = 10000;
SET @ticks_per_day = 24 * 3600 * 1000 * @ticks_per_millisecond;
SET @millisecond_of_time =
    DATEPART(MILLISECOND, @current_datetime)
    + (DATEPART(SECOND, @current_datetime)) * 1000
    + (DATEPART(MINUTE, @current_datetime)) * 60000
    + (DATEPART(HOUR, @current_datetime)) * 3600000;
SET @ticks_of_days = CAST(@days_from_0001_to_1900 + DATEDIFF(DAY, 0, @current_datetime) AS BIGINT) * @ticks_per_day;
SET @ticks_of_time = @millisecond_of_time * @ticks_per_millisecond;
SET @ticks = @ticks_of_days + @ticks_of_time;

SELECT @current_datetime, @ticks,DATEADD(s,cast(@ticks-621355968000000000 as bigint) / power(10,7),'1970-01-01 00:00:00')

本文地址：https://blog.****.net/u010194236/article/details/107664494