從 Unix 日期獲取年份 DATEPART

在我的數據庫中，日期保留為自 1970 年 1 月 1 日以來/之前經過的天數，我理解為 Unix 日期格式，例如。今天是 2018 年 5 月 23 日，值為 17674。

我正在嘗試select針對日期表中的列執行語句以僅顯示年份。

如果我執行這個：

datepart(year, c.cko_date)

我得到了 1940 年代的日期（它們應該是最近幾年）。

當我跑

select datepart(year, getdate()) 

它正確地告訴我日期是 2018 年。

關於我做錯了什麼的任何建議？

假設該列是一個int，而不是一個varchar列，你可以這樣做：

datepart(year, c.cko_date + 25567)

25567 是 1900-01-01 和 1970-01-01 之間的天數；本質上，這調整了 unix 和 SQL Server 日期時間之間的差異。

SQL Serverdatetime是在 1900 年 1 月 1 日左右設計的，即“紀元”。將 SQL Server 日期視為表示 1900 年 1 月 1 日之前或之後的時間量的十進制值可能會有所幫助。時間是小數部分，例如午夜是 0.0，中午是 0.5，下午 6 點是 0.75。可以儲存在datetime列中的值的有效範圍是1753-01-01 00:00:00to 9999-01-01 23:59:59.997。在數值上，這對應於從 開始到-53690.0000000結束的範圍2958463.99999998。

Unix1970-01-01 00:00:00用作它的 epoch。按照慣例，紀元以通用協調時間或 UTC 開始。對於北美，中央夏令時時區比 UTC 晚 5:00 小時。因此，要將值從 Unix 紀元時間值準確轉換為CDT 中的日期時間值，需要針對 5 小時的差異進行調整。因此，從 Unix 時間轉換為以天數表示的稍微更準確的範例是：

--declare an integer representing the number of days since 1970-01-01
DECLARE @days_since_unix_epoch int = 17674; --2018-05-23
--show the datetime value in UTC
SELECT UTC = CONVERT(datetime, @days_since_unix_epoch + 25567)
--show the datetime value in North American CDT
  , CDT = CONVERT(datetime, @days_since_unix_epoch + 25567) - CONVERT(datetime, '05:00:00');

結果：

╔═════════════════════════╦═════════════════════════╗
║ UTC ║ CDT ║
╠═════════════════════════╬═════════════════════════╣
║ 2018-05-23 00:00:00.000 ║ 2018-05-22 19:00:00.000 ║
╚═════════════════════════╩═════════════════════════╝

雖然我們是技術人員，但通常 Unix 紀元儲存為自 以來的毫秒數1970-01-01 00:00:00，而不是天數。如果考慮到極高的準確性，那麼毫秒數可能很難處理，因為自 1970 年以來為了處理閏秒已經對時間進行了許多調整。根據我剛剛連結到的維基百科文章，自 1972 年以來，時間已經增加了 26 個一秒；要準確處理 1970 年到今天之間的日期，您需要適當添加 0 到 26 秒。我不確定 SQL Server 的datetime結構是否能準確理解這些調整。

另請注意，SQL Server 的datetime結構具有 3 毫秒的解析度。考慮以下：

SELECT [1ms] = CONVERT(datetime, '2018-05-22 00:00:00.001')
    , [2ms] = CONVERT(datetime, '2018-05-22 00:00:00.002')
    , [3ms] = CONVERT(datetime, '2018-05-22 00:00:00.003')

輸出：

╔═════════════════════════╦═════════════════════════╦═════════════════════════╗
║ 1 毫秒 ║ 2 毫秒 ║ 3 毫秒 ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2018-05-22 00:00:00.000 ║ 2018-05-22 00:00:00.003 ║ 2018-05-22 00:00:00.003 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╝

如果您需要更好的解析度，您應該考慮使用datetime2數據類型，因為它支持微秒精度，以及可能更大的日期和時間範圍：

日期範圍：0001-01-01 到 9999-12-31

時間範圍：00:00:00 到 23:59:59.9999999

引用自：https://dba.stackexchange.com/questions/207616