Developing Matt

My Technical Journal

Years, Months, Days Passed

leave a comment »

How many years, days, and months have passed since day X.?  Good question.  Dane posted a good solution here

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int

SELECT @date = ‘2/29/04’

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate,
GETDATE()) – CASE WHEN MONTH(@date) > MONTH(GETDATE())
THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)

SELECT
@months = DATEDIFF(m, @tmpdate, GETDATE()) –
CASE WHEN DAY(@date) > DAY(GETDATE())
THEN 1
ELSE 0 END

SELECT @tmpdate =
DATEADD(m, @months, @tmpdate)

SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days

 

Thanks Dane!

Advertisements

Written by matt

February 20, 2009 at 9:04 am

Posted in T-Sql

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: