How DateDiff Function Works - 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

Transact-SQL (2000)

How DateDiff Function Works

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-28 : 07:45:47

I want to know how datediff function works.

SET DATEFORMAT MDY
set nocount on
declare @from_date varchar(12), @to_date varchar(12)
select @from_date = '06/01/2004'
select @to_date = '06/12/2004'
SELECT DATEDIFF(wk, @from_date, @to_date)

it calculates one weeks while there are more than 7 days. Why it is behaving like this?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-28 : 07:54:53

because it only give full weeks as the return type is an int... If you want partial weeks, then find the amount of days and divide by 7...

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-28 : 07:57:26

Yeah something like this:

select cast(datediff(dd, '2004-06-01', '2004-06-12') as decimal(18, 2)) / 7

Duane.

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-28 : 15:50:32

What DateDiff really counts is transitions. Since the first day of the week is normally Sunday, on my machine the following statement:

SELECT DateDiff(week, '2004-06-27', '2004-07-10')
,  DateDiff(day, '2004-06-27', '2004-07-10')

returns:

----------- ----------- 
          1          13

If you want fractional values, use a smaller unit of measure (say, minutes), then divide down to get a fractional result.

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources