CODEWITHSUNDEEP
sql-serverepoch
freeman
freeman

Reputation: 419

How to get the epoch time in 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.

Upvotes: 9

Views: 48081

Answers (3)

Stephen Turner
Stephen Turner

Reputation: 7314

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.

Upvotes: 8

Olivier Faucheux
Olivier Faucheux

Reputation: 2606

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

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

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

Upvotes: -1

Mitch Wheat
Mitch Wheat

Reputation: 300549

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

Upvotes: 10

Related Questions