Creating Birth Date from Day, Month, Year fields

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

Creating Birth Date from Day, Month, Year fields

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-14 : 12:28:28

I have 3 fields (DOB_YEAR, DOB_MO, DOB_DAY)

They are populated like this (1985, 12, 21)

I would like to take the 3 values of populate one field (BIRTH_DATE) with these combined values to look like this (19851221) BIRTH_DATE is an 8 character field in datetime format.

The code

UPDATE DOB
SET BIRTH_DATE = (dob_year+dob_mo+dob_day)

Returns 6/11/1905 for 1959,11,17

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-14 : 12:36:22

it does not matter what the data looks like in the database. really. i kid you not.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-14 : 12:39:47

I know I want to insert this line in there, but Im not sure where:

CONVERT(varchar(8), GETDATE(), 112)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 12:49:18

Yours didn't work because the sum of those values = 1987, which in datetime means 1987 days since Jan 0, 1900 which corresponds to the date it returned.

Do a search of the forum on Date & Time and it will be insightful and usefull to you.

(although it really doesn't matter what it "looks" like in the database.Really).

Since you already have the 3 fields, you don't need to have a datetime field also. For presentation, that should be done on the client side.

Poor planning on your part does not constitute an emergency on my part.

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-14 : 12:56:52

Got it. But just for closure of this thread- this is the code that works

update dob
set birth_date = dateadd(month,(12*DOB_YEAR)-22801+DOB_MO,DOB_DAY-1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 13:16:12

Why would you like to change it? It now only occupies 4 bytes per record.

SELECT REPLACE(STR(DOB_YEAR, 4) + STR(DOB_MONTH, 2) + STR(DOB_DAY, 2), ' ', '0')

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources