SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 A More Precise DateDiff Function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 02/07/2007 :  06:42:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 02/07/2007 15:14:37

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 02/07/2007 :  06:48:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 03/05/2007 01:28:38
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 03/05/2007 :  00:32:59  Show Profile  Reply with Quote
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.


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 03/05/2007 :  01:26:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 03/05/2007 01:30:03
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 03/05/2007 :  01:29:53  Show Profile  Reply with Quote
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.




KH


Edited by - khtan on 03/05/2007 01:32:22
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 03/05/2007 :  01:37:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Strange indeed.
Try the other approach, with < 1


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 03/05/2007 01:42:12
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 03/05/2007 :  01:40:03  Show Profile  Reply with Quote
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.



KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 03/05/2007 :  01:43:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 03/05/2007 :  01:45:30  Show Profile  Reply with Quote
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 .


KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 03/05/2007 :  01:51:31  Show Profile  Reply with Quote
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 ?


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 03/05/2007 :  01:56:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 03/05/2007 :  02:05:51  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
"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"

Edited by - harsh_athalye on 03/05/2007 02:10:23
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 03/05/2007 :  02:59:34  Show Profile  Reply with Quote
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.




KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 03/05/2007 :  03:03:28  Show Profile  Reply with Quote
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.



KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 02/15/2008 :  08:10:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000