RSS

Functions To Calc Your Agg

02 Sep

—This Script Will return your agg in (Y-M-D)

Set Dateformat DMY

GO

DECLARE @date datetime,
@tmpdate datetime,
@years int, @months int,
@days int SELECT @date = ’01/10/1985′
SELECT @tmpdate = @date SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) – CASE
WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date)
= MONTH(GETDATE()) AND DAY(@date) > DAY(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
—Use the following formula to calculate a person’s age in years
DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = ’01/10/1985′
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

–Notes:
–Substitute “getdate()” for the @yyyymmdd variable if you want to determine the person’s age right now.
–The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.

select datediff (year, convert (datetime, ’01/10/1985′), getdate())

DECLARE @BirthDate DATETIME
DECLARE @CurrentDate DATETIME

SELECT @CurrentDate = ’02/09/2011′, @BirthDate = ’01/10/1985′

SELECT
DATEDIFF(YY, @BirthDate, @CurrentDate) –
CASE
WHEN(
(MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
(MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
) THEN 1
ELSE 0
END

 
Leave a comment

Posted by on September 2, 2011 in Script

 

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