DOB Conversion problem - 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

Transact-SQL (2000)

DOB Conversion problem

Author

Topic

mpinnock
Starting Member

3 Posts

Posted - 2006-11-14 : 20:56:11

Hello,

I am new to this forum, so my apologies if I am posting in the wrong area or asking overly simple questions.

I have two database tables - one with a Date of birth entry (mmddyyyy) - and the second table with a date range column ( 13-24, 25-44, 45-60, etc.). I need to create a script that will read in the DOB column and convert it into a date range and update the second table.

Thanks for your help
Michael

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-14 : 21:12:27

What is the data type for the date range column >
Can you post the structure of both table ?

mpinnock
Starting Member

3 Posts

Posted - 2006-11-14 : 21:22:42

The data type for table#1 with the DOB field is datetime 8 characters and for table#2 the one with the age range is varchar 13 characters. Sorry I don't have access to the server to provide the structure at this time.

Thanks
Michael

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-14 : 21:33:07

What does the date range column represent? Age in years?

It doesn't seem like good idea to store age range in a table, since that is something that is always changing. Why not just calculate it when you need it?

If you need to calculate age, you can use the functions on these links.

This function returns age in format YYYY MM DD.
Age Function F_AGE_YYYY_MM_DD:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729

This function returns age in years.
Age Function F_AGE_IN_YEARS:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-14 : 21:50:27

"Why not just calculate it when you need it?"
Yes MVJ is right. And you might not required the 2nd table at all


declare @table table
(
	dob	datetime
)

insert into @table
select	'19910407'	union all
select	'19851231'	union all
select	'19600826'

select	[13 - 24] = count(case when age between 13 and 24 then 1 end),
	[25 - 44] = count(case when age between 25 and 44 then 1 end),
	[45 - 60] = count(case when age between 45 and 60 then 1 end)
from
(
	select 	age = dbo.F_AGE_IN_YEARS(dob, getdate())
	from	@table
) a

mpinnock
Starting Member

3 Posts

Posted - 2006-11-15 : 19:23:42

Guys,

Thanks much for your response - I will be trying your suggestions out in the next couple of days.

Cheers
Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources