纪元时间转SQL Server datetime报错及获取前3日数据的技术问询

Hey there! Let's tackle your two SQL Server problems one by one—since you're coming from Oracle, it makes total sense that the syntax differences are tripping you up right now.

1. Fixing the Epoch Time Conversion Overflow Error #

The Arithmetic overflow error you're hitting happens because SQL Server's standard DATEADD(ms, ...) function expects the interval value to be an int type. The maximum value for an int in SQL Server is 2,147,483,647, and your epoch millisecond value (1430607256000) is way larger than that—hence the overflow.

Here are two reliable fixes depending on your SQL Server version:

Option 1: For SQL Server 2022 or newer #

Microsoft added DATEADD_BIG in SQL Server 2022, which accepts bigint values directly. This is the simplest solution if you're on a recent version:

SELECT DATEADD_BIG(ms, CAST(1430607256000 AS BIGINT), '1970-01-01 00:00:00.0');

Option 2: For older SQL Server versions (2019 and earlier) #

Split the epoch milliseconds into seconds and leftover milliseconds, then combine them using two DATEADD calls. This works because the total seconds will fit within the int limit for your timestamp:

DECLARE @epochMilliseconds BIGINT = 1430607256000;
SELECT DATEADD(ms, @epochMilliseconds % 1000, DATEADD(second, @epochMilliseconds / 1000, '1970-01-01 00:00:00.0'));

2. Querying Data for the Full Day current_date-3 #

First, note that SQL Server doesn't have a current_date function like Oracle does—instead, use CAST(GETDATE() AS DATE) to get the current date without the time component.

To safely get all records from the full day three days ago, the best approach is to use a half-open interval (this avoids missing records with sub-second precision, like 23:59:59.997):

SELECT *
FROM your_table_name
WHERE your_datetime_column >= DATEADD(day, -3, CAST(GETDATE() AS DATE))
  AND your_datetime_column < DATEADD(day, -2, CAST(GETDATE() AS DATE));

This query includes all records starting at 00:00:00 of the target day, and stops right before 00:00:00 of the next day—covering every possible timestamp in between.

If you specifically need to use the 23:59:59 end time (though this is less reliable for datetime values with milliseconds), you can adjust it like this:

SELECT *
FROM your_table_name
WHERE your_datetime_column BETWEEN
    DATEADD(day, -3, CAST(GETDATE() AS DATE))
    AND DATEADD(millisecond, -1, DATEADD(day, -2, CAST(GETDATE() AS DATE)));

内容的提问来源于stack exchange，提问作者Binayak Chatterjee