Populate datetime field from text field

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 2005 Forums

Transact-SQL (2005)

Populate datetime field from text field

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-12-08 : 16:51:25

Here is a tablevar declared
declare @Birthday1 table
(
birthmonth char(2),
birthday char(2),
birthyear char(4),
ID char(10),
birthday dateime
)
insert into @Birthday1 (birthmonth,birthday,birthyear,ID,birthday)
select birthmonth,birthday,birthyear,ID,birthday
from birth_table
Sample output
birthmonth birthday birthyear ID birthday
03 03 1939 2 1939-01-01 00:00:00
04 04 1940 3 null

objective#1
Notivce that in the case of ID#2 their birthday is a null.
I am trying to populate the birthday field with the birthmonth,birthday and birthyear from the three separate fields, which are text. This will enable me to use the birthday field in calculating the age

objective#2
How would I now use the populated birthday field to determine the age of the person

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-08 : 18:37:13

I don't see how 03,03,1939 becomes '1939-01-01 00:00:00', but once you have your b'day as a date, use DATEDIFF

OBJECTIVE 1

UPDATE table
SET birthday = birthyear+birthmonth+birthday (you have two birthday fields, change one of the names).

Objective 2

SELECT datediff(day,birthday,getdate())/365 will give you the age in years for the person.

Jim

N.B select datediff(year,'12/31/2009','01/01/2010) will give you 1 year. That's why it's better to days and divide by 365

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources