How to get the epoch time in SQL Server?

Question

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.

Answer 1

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

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

Answer 2

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

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

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

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.

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.