如何在SQL Server中获取纪元时间？

The scenario is this: select max date from some table, when the target table having no data, so the max date is null. when the date being null, I want to get the earliest date of system, so the epoch time seems perfect.

场景是这样的：当目标表没有数据时，从某个表中选择最大日期，因此最大日期为空。当日期为空时，我想获得系统的最早日期，因此时代似乎是完美的。

I have searched some ways including DATEADD functions, but that seems not elegant.

我搜索了一些方法，包括DATEADD函数，但这看起来并不优雅。

3 个解决方案

#1

If I understand your question correctly, in SQL Server the epoch is given by cast(0 as datetime) :

如果我正确理解你的问题，在SQL Server中，epoch由cast（0为datetime）给出：

select Max(ISNULL(MyDateCol, cast(0 as datetime)))
from someTable
group by SomeCol

#2

The earliest date that can be stored in a SQL datetime field depends on the data type you use:

可以存储在SQL datetime字段中的最早日期取决于您使用的数据类型：

datetime:
    1753-01-01 through 9999-12-31

smalldatetime:
    1900-01-01 through 2079-06-06

date, datetime2 and datetimeoffset:
    0001-01-01 through 9999-12-31

For more exact details see from https://msdn.microsoft.com/en-GB/library/ms186724.aspx#DateandTimeDataTypes

有关更多详细信息，请参阅https://msdn.microsoft.com/en-GB/library/ms186724.aspx#DateandTimeDataTypes

The epoch is useful if you are converting numbers to dates, but irrelevant if you are storing dates as dates.

如果要将数字转换为日期，则epoch非常有用，但如果将日期存储为日期则无关紧要。

If you don't want to deal with nulls properly the simple answer is to pick a date that you know will be before your data and hard-code it into your query. cast('1900-01-01' as datetime) will work in most cases.

如果您不想正确处理空值，那么简单的答案就是选择一个您知道将在数据之前的日期并将其硬编码到查询中。 cast（'1900-01-01'作为日期时间）在大多数情况下都适用。

While using something like cast(0 as datetime) produces the same result it obscures what you have done in your code. Someone maintaining it, wondering where these odd old dates come from, will be able to spot the hard coded date more quickly.

使用像cast这样的东西（0作为日期时间）产生相同的结果时，它会模糊你在代码中所做的事情。有人维护它，想知道这些奇怪的旧日期来自哪里，将能够更快地发现硬编码日期。

#3

If you define the epoch as Jan 1, 1970: The better: to store it in a variable

如果将时期定义为1970年1月1日：更好：将其存储在变量中

DECLARE @epoch DATETIME
SET     @epoch = CONVERT( DATETIME, '01 JAN 1970', 106 )

select
 DATEPART(YEAR,  DATEADD(day, 180, @epoch)) as year,
...

#1