Date Difference - 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)

Date Difference

Author

Topic

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-04-29 : 03:32:22

Hi all,
I am trying to find difference of 2 dates (11-12-1974 and 04-29-2002). I want to receive the exact difference - how many years, months and days? Could anyone help me with a tip?
Thanks in advance.

The Rebel

nr
SQLTeam MVY

12543 Posts

Posted - 2002-04-29 : 03:40:46

You could use datepart on @date2 - @date1.
Dates are held as decimal numbers with the integer part being the day number from 1 jan 1900 and the decimal part being the time so arithmetic works.

If you want the boundaries between the two dates use datediff.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-04-29 : 04:47:24

Thanks.
However I wish to determine the age of a particular person as:
XX years, XX months, XX days. Is it possible?
Thanks in advance.

The Rebel

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-29 : 06:07:07

Sally Fribble was born on 1972-02-29. Today is 2002-02-28. How old is she in years, months and days?

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-04-29 : 06:16:40

Yes, I mean exactly this. How could I determine the age?

quote:

Sally Fribble was born on 1972-02-29. Today is 2002-02-28. How old is she in years, months and days?

The Rebel

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-29 : 06:33:36

No, I'm asking you what you want the answer to be to that particular case!
Do you want 30 years, 0 months, 0 days?
Or 29 years, 11 months, 30 days?

Edited by - Arnold Fribble on 04/29/2002 06:44:36

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-04-29 : 06:44:14

I need: 29 years, 11 months, 28 days. Is it OK?

quote:

No, I'm asking you what you want the answer to be to that particular case!
Do you want 30 years, 0 months, 0 days?
Or 29 years, 11 months, 28 days?

Edited by - Arnold Fribble on 04/29/2002 06:34:54

The Rebel

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-29 : 07:12:21

Sorry, I meant 30 days -- subtracted 1 instead of adding.
But from 1972-02-29 to 2002-03-01 is 30 years, 0 months, 1 day, right?

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-04-29 : 07:14:40

Yes, sure. I just mean that I need a result in the format: XX years, XX months, XX days.

quote:

Sorry, I meant 30 days -- subtracted 1 instead of adding.
But from 1972-02-29 to 2002-03-01 is 30 years, 0 months, 1 day, right?

The Rebel

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-29 : 08:38:16

It's a bit messy, but here goes....

select DateDiff(m, Date1, Date2)/12 as Years, 
       DateDiff(m, Date1, Date2)%12 as Months, 
       DateDiff(d, DateAdd(m, DateDiff(m, Date1, Date2), Date1), Date2) as Days

where Date1 is the date of birth
and Date2 is the 'Age At' date

Edited by - timmy on 04/29/2002 08:55:22

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-04-29 : 08:44:25

DECLARE @year int
DECLARE @month int
DECLARE @day int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = '1972-02-29'
SELECT @yyyymmdd ='2002-03-01' --GETDATE()

SELECT @month = datediff(month, @dob, @yyyymmdd)
SELECT @day = DateDiff("d", DateAdd(month, @month, @dob), @yyyymmdd)
IF @day < 0
BEGIN
SELECT @month = @month - 1
SELECT @day = DateDiff(day, DateAdd(month, @month, @dob), @yyyymmdd)
END

SELECT @year = floor(@month / 12)
SELECT @month = @month % 12

SELECT @day AS Days, @month AS Months , @year AS Years

This sounds like what you are needing. I found it on the web (somewhere?) a while ago. You can make a function of it.

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-04-29 : 08:55:25

Thank you guys.

The last propolsal works excellent!!!
Thank you 'YellowBug'. The example of 'timmy' works well but in some particular cases is not perfect. But anyway thank you all for your help.

Regards.

The Rebel

Edited by - vladimir_grigoro on 04/30/2002 01:28:05

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-29 : 10:56:18

So with:
SELECT @dob = '1972-02-29'
SELECT @yyyymmdd ='2002-02-28'

We get:
Days Months Years
0 0 30

Which is why I asked the question in the first place.

dsdeming

479 Posts

Posted - 2002-04-29 : 13:00:46

Can you provide a date for which timmy's code doesn't work? I've tested it with dates going back to 1904 ( to get back past '50 ), and I don't see a problem.

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-04-30 : 01:34:25

Hi,
As I said the example is pretty good but in some cases return negative numbers for the days, For example:

declare @date1 datetime, @date2 datetime
select @date1='1976-05-31'
select @date2='2002-04-29'
select DateDiff(m, @Date1, @Date2)/12 as Years,
DateDiff(m, @Date1, @Date2)%12 as Months,
DateDiff(d, DateAdd(m, DateDiff(m, @Date1, @Date2), @Date1), @Date2) as Days

This returning: 25 years, 11 months and -1 days. However Timmy's example is simple and it could be useful for rough calculations.

quote:

Can you provide a date for which timmy's code doesn't work? I've tested it with dates going back to 1904 ( to get back past '50 ), and I don't see a problem.

The Rebel

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-04-30 : 01:37:54

Yes, thats the answer and I think that it's correct. What you mean by that example?

quote:

So with:
SELECT @dob = '1972-02-29'
SELECT @yyyymmdd ='2002-02-28'

We get:
Days Months Years
0 0 30

Which is why I asked the question in the first place.

The Rebel

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-30 : 06:18:47

When I first asked you what you wanted for 1972-02-29 to 2002-02-28. You said 29 years, 11 months, etc. rather than 30 years exactly. Now you say you want 30 years exactly. That's what I meant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources