A More Precise DateDiff Function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

A More Precise DateDiff Function

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 06:42:24

This will give you the time information about how apart two dates are.

CREATE FUNCTION dbo.fnTimeApart
(
	@FromTime DATETIME,
	@ToTime DATETIME
)
RETURNS @Time TABLE ([year] SMALLINT, [month] TINYINT, [day] TINYINT, [hour] TINYINT, [minute] TINYINT, [second] TINYINT, [millisecond] SMALLINT)
AS
BEGIN
	DECLARE	@Temp DATETIME,
		@Mts INT,
		@year SMALLINT,
		@month TINYINT,
		@day TINYINT,
		@hour TINYINT,
		@minute TINYINT,
		@second TINYINT,
		@millisecond SMALLINT

	IF @FromTime > @ToTime
		SELECT	@Temp = @FromTime,
			@FromTime = @ToTime,
			@ToTime = @Temp

	SET	@Mts =	CASE
				WHEN DATEPART(day, @FromTime) <= DATEPART(day, @ToTime) THEN 0
				ELSE -1
			END + DATEDIFF(month, @FromTime, @ToTime)

	SELECT	@year = @Mts / 12,
		@month = @Mts % 12,
		@Temp = DATEADD(month, @Mts, @FromTime)

	SELECT	@day = datediff(hour, @Temp, @ToTime) / 24,
		@Temp = DATEADD(day, @day, @Temp)

	SELECT	@hour = DATEDIFF(minute, @Temp, @ToTime) / 60,
		@Temp = DATEADD(hour, @hour, @Temp)

	SELECT	@minute = DATEDIFF(second, @Temp, @ToTime) / 60,
		@Temp = DATEADD(minute, @minute, @Temp)

	SELECT	@second = DATEDIFF(millisecond, @Temp, @ToTime) / 1000,
		@Temp = DATEADD(second, @second, @Temp),
		@millisecond = DATEDIFF(millisecond, @Temp, @ToTime)

	INSERT	@Time (year, month, day, hour, minute, second, millisecond)
	SELECT	@year,
		@month,
		@day,
		@hour,
		@minute,
		@second,
		@millisecond

	RETURN
END

And to test the function

SELECT		d.FromDate,
		d.ToDate,
		x.*
FROM		(
			SELECT	'19690906' AS FromDate, '19760608' AS ToDate UNION ALL
			SELECT	'19991231', '20000101' UNION ALL
			SELECT	'20070207', '20070208' UNION ALL
			SELECT	'20000131', '20000228' UNION ALL
			SELECT	'20070202', '20070201' UNION ALL
			SELECT	'20070207', '20070307' UNION ALL
			SELECT	'20000131', '20000301' UNION ALL
			SELECT	'20011231 15:24:13.080', '20020101 17:15:56.343' UNION ALL
			SELECT	'20011231 17:15:56.343', '20020101 15:24:13.080' UNION ALL
			SELECT	'20020101 15:24:13.080', '20011231 17:15:56.343' UNION ALL
			SELECT	'20000131', '20000229'
		) AS d
CROSS APPLY	dbo.fnTimeApart(d.FromDate, d.ToDate) AS x
ORDER BY	d.FromDate,
		d.ToDate

And the output is

FromDate		ToDate			year	month	day	hour	minute	second	millisecond
19690906		19760608		6	9	2	0	0	0	0
19991231		20000101		0	0	1	0	0	0	0
20000131		20000228		0	0	28	0	0	0	0
20000131		20000229		0	0	29	0	0	0	0
20000131		20000301		0	1	1	0	0	0	0
20011231 15:24:13.080	20020101 17:15:56.343	0	0	1	1	51	43	263
20011231 17:15:56.343	20020101 15:24:13.080	0	0	0	22	8	16	736
20020101 15:24:13.080	20011231 17:15:56.343	0	0	0	22	8	16	736
20070202		20070201		0	0	1	0	0	0	0
20070207		20070208		0	0	1	0	0	0	0
20070207		20070307		0	1	0	0	0	0	0

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 06:48:09

This is a SQL 2000 version

CREATE FUNCTION dbo.fnTimeApart2000
(
	@FromTime DATETIME,
	@ToTime DATETIME
)
RETURNS NUMERIC(17, 9)
AS
BEGIN
	DECLARE	@Temp DATETIME,
		@Mts INT,
		@year SMALLINT,
		@month TINYINT,
		@day TINYINT,
		@hour TINYINT,
		@minute TINYINT,
		@second TINYINT,
		@millisecond SMALLINT

	IF @FromTime > @ToTime
		SELECT	@Temp = @FromTime,
			@FromTime = @ToTime,
			@ToTime = @Temp

	SET	@Mts =	CASE
				WHEN DATEPART(day, @FromTime) <= DATEPART(day, @ToTime) THEN 0
				ELSE -1
			END + DATEDIFF(month, @FromTime, @ToTime)

	SELECT	@year = @Mts / 12,
		@month = @Mts % 12,
		@Temp = DATEADD(month, @Mts, @FromTime)

	SELECT	@day = datediff(hour, @Temp, @ToTime) / 24,
		@Temp = DATEADD(day, @day, @Temp)

	SELECT	@hour = DATEDIFF(minute, @Temp, @ToTime) / 60,
		@Temp = DATEADD(hour, @hour, @Temp)

	SELECT	@minute = DATEDIFF(second, @Temp, @ToTime) / 60,
		@Temp = DATEADD(minute, @minute, @Temp)

	SELECT	@second = DATEDIFF(millisecond, @Temp, @ToTime) / 1000,
		@Temp = DATEADD(second, @second, @Temp),
		@millisecond = DATEDIFF(millisecond, @Temp, @ToTime)

	RETURN	10000 * @year + 100 * @month + @day + @hour / 100.0 + @minute / 10000.0 + @second / 1000000.0 + @millisecond / 1000000000.0
END

This test code

SELECT		d.FromDate,
		d.ToDate,
		dbo.fnTimeApart2000(d.FromDate, d.ToDate)
FROM		(
			SELECT	'19690906' AS FromDate, '19760608' AS ToDate UNION ALL
			SELECT	'19991231', '20000101' UNION ALL
			SELECT	'20070207', '20070208' UNION ALL
			SELECT	'20000131', '20000228' UNION ALL
			SELECT	'20070202', '20070201' UNION ALL
			SELECT	'20070207', '20070307' UNION ALL
			SELECT	'20000131', '20000301' UNION ALL
			SELECT	'20011231 15:24:13.080', '20020101 17:15:56.343' UNION ALL
			SELECT	'20011231 17:15:56.343', '20020101 15:24:13.080' UNION ALL
			SELECT	'20020101 15:24:13.080', '20011231 17:15:56.343' UNION ALL
			SELECT	'20000131', '20000229'
		) AS d
ORDER BY	d.FromDate,
		d.ToDate

produces these values


FromDate		ToDate			TimeApart (YYYYMMDD.HHMMSSttt)
19690906		19760608		60902.000000000  -- 6 years, 9 months and 2 days
19991231		20000101		    1.000000000  -- 1 day
20000131		20000228		   28.000000000  -- 28 days
20000131		20000229		   29.000000000  -- 29 days
20000131		20000301		  101.000000000  -- 1 month and 1 day
20011231 15:24:13.080	20020101 17:15:56.343	    1.015143263  -- 1 day, 1 hour, 51 minutes, 43 seconds and 263 milliseconds
20011231 17:15:56.343	20020101 15:24:13.080	    0.220816736  -- 22 hours, 8 minutes, 16 seconds and 736 milliseconds
20020101 15:24:13.080	20011231 17:15:56.343	    0.220816736  -- 22 hours, 8 minutes, 16 seconds and 736 milliseconds
20070202		20070201		    1.000000000  -- 1 day
20070207		20070208		    1.000000000  -- 1 day
20070207		20070307		  100.000000000  -- 1 month

For example, to determine if a date is birthday, use GETDATE() as second parameter and check TimeApart value with

SELECT e.* FROM Employees AS e WHERE dbo.fnTimeApart2000(e.BornDate, GETDATE()) % 10000 < 1

EDIT: Suggestions made by Khtan

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 00:32:59

The fnTimeApart() (SQL 2000 version) returns a numeric data type. It can't perform modulus operation with numeric data type

quote:
SELECT e.* FROM Employees AS e WHERE convert(int, dbo.fnTimeApart(e.BornDate, GETDATE())) % 10000 = 0

Another thing, the udf name used in your example is diff from your script.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-05 : 01:26:39

Or just

SELECT e.* FROM Employees AS e WHERE FLOOR(dbo.fnTimeApart(e.BornDate, CURRENT_TIMESTAMP)) % 10000 = 0

Or even better
SELECT e.* FROM Employees AS e WHERE dbo.fnTimeApart2000(e.BornDate, GETDATE()) % 10000 < 1

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 01:29:53

Nope. SQL don't like it

select	FLOOR(dbo.fnTimeApart('2007-01-01', CURRENT_TIMESTAMP)) % 10000

Server: Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with void type
Server: Msg 8117, Level 16, State 1, Line 1
Operand data type numeric is invalid for modulo operator.

Strange. FLOOR suppose to return integer

quote:

FLOOR
Returns the largest integer less than or equal to the given numeric expression.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-05 : 01:37:14

Strange indeed.
Try the other approach, with < 1

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 01:40:03

Not working also


use Northwind
SELECT e.* FROM Employees AS e WHERE dbo.fnTimeApart2000(e.BirthDate, GETDATE()) % 10000 < 1
Server: Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with void type
Server: Msg 8117, Level 16, State 1, Line 2
Operand data type numeric is invalid for modulo operator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-05 : 01:43:43

My laptop SQL 2000 sp4 must be broken, because all of the above suggestions works for me...
Well, then you have to do a convert/cast before checking.

Thank you for spotting this out for me, khtan.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 01:45:30

quote:
Originally posted by Peso

My laptop SQL 2000 sp4 must be broken, because all of the above suggestions works for me...
Well, then you have to do a convert/cast before checking.

Thank you for spotting this out for me, khtan.

Peter Larsson
Helsingborg, Sweden

Oh maybe it is mine that is broken

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 01:51:31

I just tried on another SQL Server 2000 SP4. Same error message. However the codes works for 2005

select  dbo.fnTimeApart2000('2007-01-01', GETDATE()) % 10000

Maybe you are testing it on 2005 ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-05 : 01:56:52

Have two instances on my laptop, sql 2000 and sql 2005. Both works.
Not a big deal. Just cast/convert and you have the result.

Well done!

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-05 : 02:05:51

"FLOOR suppose to return integer"

That's not true.

As per BOL:

quote:
Return Types
Returns the same type as numeric_expression.

So even though the result looks like integer, it's not.

Check this:

Select datalength(floor(5.777))  -- 5 bytes
Select datalength(floor(5)) -- 4 bytes

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 02:59:34

quote:
Originally posted by harsh_athalye

"FLOOR suppose to return integer"

That's not true.

As per BOL:

quote:
Return Types
Returns the same type as numeric_expression.

So even though the result looks like integer, it's not.

Check this:

Select datalength(floor(5.777)) -- 5 bytes
Select datalength(floor(5)) -- 4 bytes

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

You are right. How can i missed this part

quote:
Return Types
Returns the same type as numeric_expression.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 03:03:28

It seems that modulus implementation in SQL 2005 is different from 2000.
this will give error in 2000 but not in 2005.

select	a = 10.5 % 3
/*
a    
---- 
1.5
*/

from BOL 2005

quote:
Provides the integer remainder after dividing the first numeric expression by the second one.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 08:10:22

To test both

select view1.*, age.*, dbo.fntimeapart2000(fromdt, todt) AS [2000 version]
from 
 ( 
  select convert(datetime,'20000229') fromdt, 
         dateadd(dd,number,'20040127') todt 
  from master..spt_values 
  where type='P' 
    and number<40
 ) view1 
cross apply dbo.fnTimeApart(fromdt,todt) as age

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources