SQL function calculating age based on today's date

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

General SQL Server Forums

New to SQL Server Programming

SQL function calculating age based on today's date

Author

Topic

crazyco
Starting Member

30 Posts

Posted - 2011-11-23 : 08:44:02

I wonder if anyone can help, I have a request for an SQL function which will take the first 6 digits of an ID number (which is 10 digits long) and calculate the age based on today’s date:
The first 6 digits of the ID are the person’s date of birth (ddmmyy)

Eg 0112670245 : 01/12/1967

I have to assume the century of birth as being 20 IF THE 5th & 6th digits are 00,01,02,03,04,05,06,07,08,09,10 or 11 (eg 011200 = 01/12/2000)
Otherwise take century of birth to be 19 (eg 01/12/1967 as in the example above)

I also need to use 2 other functions one validate its an ID, and one to calculate age.

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-23 : 10:44:06

Here's one of many ways:


DECLARE @IDVal VARCHAR(6)

SET @IDVal = '011267'
SELECT CONVERT(DATETIME,SUBSTRING(@IDVal,3,2) + '-' + SUBSTRING(@IDVal,1,2) + '-' + SUBSTRING(@IDVal,5,2))

From there, to calculate age is a simple datediff, to ensure its an id is based on some rules you were given for the assignment or you may need to ask more questions from the person who gave you the work.

crazyco
Starting Member

30 Posts

Posted - 2011-11-25 : 07:44:49

Thanks for your reply, have been told to go along these lines but I'm still struggling:

CREATE FUNCTION [dbo].[calc_age_from_secID]
(
-- Add the parameters for the function here
@secID VARCHAR(10)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @IDVal VARCHAR(6)

--CHECKsecID = 0 RETURN NULL

IF @secID = 0 RETURN NULL

--get left 6
SET @IDVal = '011267'
--if right 2 in 00,01,02,03,04,05,06,07,08,09,10 or 11 THEN left(dob, 4)+'20'+right(dob,2)
--SELECT CASE WHEN RIGHT(@d, 2) <= 11 THEN '20' ELSE '19' END
DECLARE @century VARCHAR(2)

SELECT

--otherwise left(dob, 4)+'19'+right(do9b,2)
--YYYY/MM/DD
--convert into date format

SELECT CONVERT(DATETIME,@century+RIGHT(dob,2)+'/'+SUBSTRING(@IDVal,3,2)+'/'+LEFT(dob, 2))

--return calc_age(dob, GETDATE())

RETURN @IDVal

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 08:30:21

Here's a start - note the test code in comments

CREATE FUNCTION [dbo].[calc_age_from_secID]
(
-- Add the parameters for the function here
@secID VARCHAR(10)
)
RETURNS varchar(8)
AS
BEGIN
/*
select [dbo].[calc_age_from_secID]('010101xxx')
select [dbo].[calc_age_from_secID]('010101xxxx')
select [dbo].[calc_age_from_secID]('010111xxxx')
select [dbo].[calc_age_from_secID]('010112xxxx')
select [dbo].[calc_age_from_secID]('010113xxxx')
*/
-- Declare the return variable here
declare @dob varchar(8)
--CHECKsecID = 0 RETURN NULL

IF len(@secID) < 10 RETURN NULL
select @dob = SUBSTRING(@secID,5,2) + SUBSTRING(@secID,3,2) + SUBSTRING(@secID,1,2)
select @dob = case when LEFT(@dob,2) <= '11' then '20' else '19' end + @dob
return (@dob)
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

crazyco
Starting Member

30 Posts

Posted - 2011-11-25 : 10:20:17

Excellent, got it working perfectly now. Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources