Problem with 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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with DateDiff function

Author

Topic

azharrahi
Starting Member

44 Posts

Posted - 2007-02-07 : 03:35:28

Hi all people . Hope you all will be fine
I am getting problem with DateDiff function.
for example when i try to take diff between '5/1/2005' and '5/1/2006' through DateDiff Function, then it returns difference 1 which is true (according to my requriment as well)
Select DateDiff(Year,'5/1/2005','5/1/2006')
returns 1
but when I take difference between '5/1/2005' and '4/1/2006' (changing the month from 5 to 4) then it still returns the '1' year difference wich is not true according to my requirement . Because the actual difference is 11 months
Select DateDiff(Year,'5/1/2005','4/1/2006')
returns 1 (its false)

I want to take the difference in years but it must return the actual differnece ie e.g
Select DateDiff(Year,'5/1/2005','5/1/2006')
must return 1 year
and
Select DateDiff(Year,'5/1/2005','4/1/2006')
must return 0 year

and
Select DateDiff(Year,'5/1/2005','5/1/2007')
must return 2 years

and
Select DateDiff(Year,'5/1/2005','4/1/2007')
must return 1 year

tell me the exact solution ... which can also be usaed for difference between months and days like years .

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 03:46:21

Take a look at these functions http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462
DATEDIFF function operates with "difference in timeslots".

SELECT DATEDIFF(year, '31 dec 2006', '1 jan 2007') returns 1 year difference even though there is only 1 day difference, because the difference in years between 2006 and 2007 is exactly 1 year.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-07 : 03:46:51

[code]Select DateDiff(month,'5/1/2005','4/1/2006')/12 as [Diff][/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

azharrahi
Starting Member

44 Posts

Posted - 2007-02-07 : 05:27:29

Harsh
Your query still contains bug for my requirement.
Select DateDiff(month,'5/4/2005','5/4/2006')/12 as [Diff] must return one year difference as your query is doing
But Select DateDiff(month,'5/4/2005','5/3/2006')/12 as [Diff] (just a day behind).. it must not return one year difference but still your query is returning one year difference.
Well think the situation of Birthday period.
my birthday is '8/27/1982' (27 aug 1982)
On '8/27/1983' ... the difference between '8/27/1982' and '8/27/1983' is 1 year and i was of one year
but on '8/27/1983' .... the difference between '8/27/1982' and '8/26/1983' is not 1 year and I was not of one year on '8/26/1983'.. becuase on '8/26/1983' i was one day behind to be of one year ... and i want that in this situation , the query must return 0 year difference between '8/27/1982' and '8/26/1983' similary returns 1 year difference for '8/27/1982' and '8/26/1984' but returns 2 years for for '8/27/1982' and '8/27/1984' and so on

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 05:32:16

Here is the same thing. The "difference in timeslot occupied" with months is still the same value!

Select DateDiff(month,'30/4/2005','1/3/2006')
Select DateDiff(month,'1/4/2005','31/3/2006')

Both produces 11 months apart...

Did you look at the link I provided?
If you did, is there something more we can help you with to explain the function?
If you did not, why did you not look at the link?

Peter Larsson
Helsingborg, Sweden

azharrahi
Starting Member

44 Posts

Posted - 2007-02-07 : 05:39:03

yes I m on the link which you have provided .. trying to understand it .. if i will fail to understand properly then i will ask from you ...

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 05:47:55

Here is a shorter code

IF DATEDIFF(month, '5/1/2005', '4/1/2006') % 12 = 0 AND DATEPART(day, '5/1/2005') > DATEPART(day, '4/1/2006')
	SELECT DATEDIFF(month, '5/1/2005', '4/1/2006') / 12 - 1
ELSE
	SELECT DATEDIFF(month, '5/1/2005', '4/1/2006') / 12

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 06:01:57

If you only care about whole dates, and not the time some person is born, the code above can help you.

But... If you also care about the time of day, this might help you.

CREATE FUNCTION dbo.fnYearsApart
(
	@FromDate DATETIME,
	@ToDate DATETIME
)
RETURNS	SMALLINT
AS
BEGIN
	DECLARE	@Diff SMALLINT,
		@F DATETIME,
		@T DATETIME

	SELECT	@Diff = DATEDIFF(month, @FromDate, @ToDate) / 12,
		@F = DATEADD(year, 1900 - DATEPART(year, @FromDate), @FromDate),
		@T = DATEADD(year, 1900 - DATEPART(year, @ToDate), @ToDate)

	IF DATEDIFF(month, @FromDate, @ToDate) % 12 = 0 AND @F > @T
		SELECT	@Diff = @Diff - 1

	RETURN	@Diff
END

Peter Larsson
Helsingborg, Sweden

azharrahi
Starting Member

44 Posts

Posted - 2007-02-07 : 07:08:25

Thanks Peso ... I m not care about time ... The query for whole date is enough and working properly. It will hopefully solve my all problems which I m getting in date handling.

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-30 : 07:34:18

http://www.sqlteam.com/item.asp?ItemID=26922

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources