How to get time in SQL Server?

Question

The scenario is this: select the maximum date from some table when the target table has no data, so the maximum date is zero. when the date is zero, I want to get the earliest date of the system, so the era time seems perfect.

I searched for some ways, including the DATEADD functions, but this seems not elegant.

Answer 1

If I understood your question correctly, in SQL Server the era is set to 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 the datetime SQL field depends on the data type used:

 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 

See https://msdn.microsoft.com/en-GB/library/ms186724.aspx#DateandTimeDataTypes for more information.

epoch is useful if you convert numbers to dates, but are irrelevant if you save dates as dates.

If you do not want to deal with zeros correctly, the simple answer is to select a date that, as you know, will be in front of your data and hard code it in your request. cast('1900-01-01' as datetime) will work in most cases.

When using something like cast(0 as datetime) produces the same result, it hides what you did in your code. Someone supporting this, wondering where these strange old dates come from, will be able to quickly determine the speed of hard coding.

Answer 3

If you define an era as January 1, 1970: better: 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, ...