Question on DateDiff - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Question on DateDiff

Author

Topic

thumsup9
Starting Member

7 Posts

Posted - 2006-08-14 : 10:54:10

The SQL below only compares the day portion and does not take into consideration the time portion...so a date entered of 2006-08-11 12:34:56.123 and a date_closed of 2006-08-14 23:59:59.999 still produces 3 days, whereas its more than 3 days if you consider the time too. This needs to be modified!

datediff(day,date_entered,date_closed) <= 3

I tried one way of doing this

datediff(ss,date_entered,date_closed) <= (3*24*60*60)

Thanks,

Do you think this is fine or do you know any other way of doing this.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-14 : 11:35:57

what about:


--of 2006-08-11 12:34:56.123 and a date_closed of 2006-08-14 23:59:59.999
DECLARE 
	@Date1	datetime,
	@Date2	datetime

SET @Date1 = '2006-08-11 12:34:56.123'
SET @Date2 = '2006-08-14 23:59:59.999'	

SELECT 
	@Date2-@Date1

granted, this has added the 1900-01 because datetime start's at 1900-01-01. Helpful?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-14 : 11:37:47

modified for comparison:


--of 2006-08-11 12:34:56.123 and a date_closed of 2006-08-14 23:59:59.999

DECLARE 
	@Date1	datetime,
	@Date2	datetime

SET @Date1 = '2006-08-11 12:34:56.123'
SET @Date2 = '2006-08-14 23:59:59.999'	

SELECT 
	CASE
		WHEN @Date2-@Date1 > cast(3 AS datetime) THEN 'Greater'
	ELSE	'Smaller or Equal'
	END AS DateDiffMoreThan3Days

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-14 : 11:37:53

Of the following, what u want to do ?


Declare @d1 datetime, @d2 datetime
Set @d1 = '2006-08-11 12:34:56.123'
Set @d2 = '2006-08-14 23:59:59.999'

Select datediff(day,@d1,@d2) as Results


Results
-------
4

u want to compare date part only ?

Srinika

thumsup9
Starting Member

7 Posts

Posted - 2006-08-14 : 11:59:17

Thanks for all your inputs, will check with the options provided and let you know which worked the best for me.

Thanks Again,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-14 : 12:35:20

or Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-14 : 12:49:26

The difference actually is 3 days plus a fraction of a day.

You haven't really explained exactly what are you looking for. Are you looking for some kind of fractional day? Days, hours, minutes, and seconds?


select
	DiffDays=datediff(dd,0,ED-SD), Diff=ED-SD
from
(
select 
	SD=convert(datetime,'2006-08-11 12:34:56.123'),
	ED=convert(datetime,'2006-08-14 23:59:59.999')
) a

DiffDays    Diff                                                   
----------- ------------------------------------------------------ 
3           1900-01-04 11:25:03.877

(1 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 03:27:21

DATEDIFF calculates the number of used "timeslots", depending on the resolution you want.

1) select datediff(hour, '12:59:59', '13:00:00') will return 1 hour "difference".
2) select datediff(minute, '12:59:59', '13:00:00') will return 1 minute difference.
3) select datediff(day, '20060815 23:59:59', '20060816 00:00:00') will return 1 day difference.

Depending on the "resolution" you want, SQL calculates the two datetimes separately, and the subtracts the latter with the former.

Example 1 then gives "hour 13" - "hour 12" which is 1 hour difference.
Example 2 then gives "minute 13:00" - "minute 12:59" which is 1 minute difference.
Example 3 then gives "day 16" - "day 15" which is 1 day difference.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources