9

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.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
freeman
  • 399
  • 1
  • 3
  • 11
  • possible duplicate of [What's the equivalent of php time() in mssql?](http://stackoverflow.com/questions/5942002/whats-the-equivalent-of-php-time-in-mssql) – Corbin Apr 01 '12 at 02:56
  • Wait... Do you mean epoch as in an epoch in general, or specifically the unix epoch? Also, the max of a non existent set being null makes sense to me. Why don't you like the null result? – Corbin Apr 01 '12 at 02:57
  • @Corbin: en... I want to use this time as a where condition. so, if it's null, I need to change where clause which is not my pretension. At the end, a epoch time seems perfect for this scenario, no changes needs to be in where clause. Did I clear myself?\ – freeman Apr 01 '12 at 05:13

3 Answers3

10

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
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
8

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.

Stephen Turner
  • 7,125
  • 4
  • 51
  • 68
-1

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,
...
SetFreeByTruth
  • 819
  • 8
  • 23
Olivier Faucheux
  • 2,520
  • 3
  • 29
  • 37