New answers tagged

8

The following solution is for SQL Server. The approach is similar to Serg's in that the query uses only the DATEADD and DATEDIFF functions. It does not, however, account for negative intervals (FromDate > ToDate), and it derives years and months from the total month difference: WITH MonthDiff AS ( SELECT t.FromDate, t.ToDate, ...


8

This answer shows an implementation using a SQL Server (2005+) CLR function. -- Enable CLR (if necessary) EXECUTE sys.sp_configure @configname = 'clr enabled', @configvalue = 1; RECONFIGURE; Assembly and function CREATE ASSEMBLY DBA AUTHORIZATION dbo FROM ...


4

PostgreSQL supports the age function out-of-the-box: select FromDate, ToDate, ExpectedResult, age(ToDate, FromDate) from TestData; This gives the desired result, give-or-take some additional time values. FromDate ToDate ExpectedResult age ---------- ---------- ---------------------------- ...


4

Version with no number table or tally required. Gives the same result on Michael Green's test data. They differ on data where @FromDate > @ToDate. ReadableInterval2 returns negative values contrary to nulls. CREATE FUNCTION dbo.ReadableInterval2( @FromDate AS date, @ToDate AS date ) RETURNS TABLE AS RETURN (with checkData as ( select ...


6

My version, implemented in SQL Server 2008R2 SP2. CREATE FUNCTION dbo.ReadableInterval( @FromDate AS date, @ToDate AS date ) RETURNS TABLE AS RETURN ( with YearStep as ( select max(n1.Number) as YearNumber from dbo.Numbers as n1 where n1.Number <= DATEDIFF(YEAR, @FromDate, @ToDate) -- see comment (A) and DATEADD(YEAR, ...


3

If the reporter had any thoughts of rigor I would expect the hot cache / cold cache information to be included in the report. Otherwise it is like saying a car costs $y without saying which option, warranty, service or taxes that includes. Similarly I would expect to see it stated that subsequent tests were performed under the same conditions and how that ...



Top 50 recent answers are included