SQL Server 2012/2014本地DateTime转UTC Unix毫秒时间戳及时间范围查询的替代方案

Got it, let's tackle this problem step by step since SQL Server 2012 and 2014 don't support DATEDIFF_BIG or AT TIME ZONE—those features landed in 2016. We need to solve two core tasks: converting local datetime values to UTC Unix millisecond timestamps, and querying records where LastUpdated falls between your target Unix millisecond range.

1. Convert Local DateTime to UTC Unix Millisecond Timestamp #

Since we can't use the newer timezone functions, we'll manually handle the timezone offset and calculate the timestamp without relying on DATEDIFF_BIG:

-- Example: Convert a local DateTime to UTC Unix millisecond timestamp
DECLARE @LocalDateTime DATETIME = '2021-05-08 10:00:00.123' -- Replace with your local datetime value
DECLARE @UtcOffsetMinutes INT = DATEPART(TZOFFSET, SYSDATETIMEOFFSET())

-- Step 1: Convert local time to UTC by subtracting the server's timezone offset
DECLARE @UtcDateTime DATETIME = DATEADD(MINUTE, -@UtcOffsetMinutes, @LocalDateTime)

-- Step 2: Calculate Unix milliseconds (avoid overflow by casting to BIGINT first)
DECLARE @UnixMs BIGINT = 
    CAST(DATEDIFF(SECOND, '1970-01-01 00:00:00', @UtcDateTime) AS BIGINT) * 1000 
    + DATEPART(MILLISECOND, @UtcDateTime)

SELECT @UnixMs AS UtcUnixMilliseconds

Quick Explanation: #

• SYSDATETIMEOFFSET() gets your server's current offset from UTC (works in 2012+).
• Casting DATEDIFF(SECOND) to BIGINT prevents integer overflow—since Unix seconds will exceed the 32-bit INT limit after 2038, this keeps calculations safe.

2. Query Records Between Target Unix Millisecond Timestamps #

Instead of calculating the timestamp for every LastUpdated row (which would ignore any index on the column), we'll reverse the process: convert your Unix millisecond range to local datetime values, then use BETWEEN to filter. This is way more efficient.

-- Define your target Unix millisecond range
DECLARE @StartUnixMs BIGINT = 1620459590247
DECLARE @EndUnixMs BIGINT = 1620467586956

-- Step 1: Convert Unix milliseconds to UTC DateTime
DECLARE @StartUtcDateTime DATETIME = DATEADD(MILLISECOND, @StartUnixMs, '1970-01-01 00:00:00.000')
DECLARE @EndUtcDateTime DATETIME = DATEADD(MILLISECOND, @EndUnixMs, '1970-01-01 00:00:00.000')

-- Step 2: Get server's UTC offset (in minutes)
DECLARE @UtcOffsetMinutes INT = DATEPART(TZOFFSET, SYSDATETIMEOFFSET())

-- Step 3: Convert UTC times to local DateTime (matches the timezone of your LastUpdated column)
DECLARE @StartLocalDateTime DATETIME = DATEADD(MINUTE, @UtcOffsetMinutes, @StartUtcDateTime)
DECLARE @EndLocalDateTime DATETIME = DATEADD(MINUTE, @UtcOffsetMinutes, @EndUtcDateTime)

-- Final query: Use the local DateTime range to filter records
SELECT * 
FROM Mytable 
WHERE LastUpadted BETWEEN @StartLocalDateTime AND @EndLocalDateTime

Important Notes: #

• Daylight Saving Time (DST): SYSDATETIMEOFFSET() returns the server's current offset. If your LastUpdated dates fall in a period with a different offset (e.g., DST was active), this might cause minor discrepancies. For full DST-aware conversion in 2012/2014, you'd need to either maintain a historical timezone offset table or use a CLR function (requires server permissions).
• Precision: SQL Server's datetime type has ~3ms precision. If your Unix timestamps have 1ms precision, you might see tiny rounding differences. For higher precision, switch to datetime2 (supported in 2012+).

内容的提问来源于stack exchange，提问作者Mahesh Reddy