Combine 3 fields into 1 - 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

General SQL Server Forums

New to SQL Server Programming

Combine 3 fields into 1

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-05 : 16:12:39

I have 3 fields: DOB_DAY (ex: 15), DOB_MO (ex: 8), and DOB_YEAR (ex: 1975). I have a blank field named BIRTH_DATE.

What would be the SQL to set the BIRTH_DATE field to be equal to the Day, Month, and Year field. I need it in "DateTime" format.

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-05 : 16:16:42

You concatenate.


Select Convert(datetime,Convert(varchar,DOB_YEAR) + '-' + Convert(varchar,DOB_MO) + '-' + Convert(varchar,DOB_DAY) )

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 16:33:35

select dateadd(month, 12 * dob_year - 22801 + dob_mo, dob_day - 1)
from table1

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-05 : 16:37:03

This will do it:


select dateadd(month,(12*DOB_YEAR)-22801+DOB_MO,DOB_DAY-1)

Converting Year, Month, and Day to DateTime:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

Edit: Yaked!

CODO ERGO SUM

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-05 : 16:41:51

When I use that command I get "Adding a value to a 'datetime' column caused overflow."

Plus, how do I tell make the field BIRTH_DATE the value of this concantonation?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-05 : 16:46:10

>>> When I use that command I get "Adding a value to a 'datetime' column caused overflow."

Sounds like you have bad values in your data that cannot be converted to a datetime.

>>> Plus, how do I tell make the field BIRTH_DATE the value of this concantonation?

Use an UPDATE statement.

CODO ERGO SUM

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-05 : 16:50:15

If I want to update and set the value of the field IMAGE_DATE (PERIMAGE.IMAGE_DATE) with the value of another table (GRAB.DATE), what would the syntax be?

They both have the same field (FCN)

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-05 : 17:09:34

Also when I use this syntax =

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

I get this error
(Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 17:16:29

Did DATEADD function name get lost in the copy & paste operation?

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

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-05 : 17:21:24

ahhh..... that was it.

I still get the "Adding a value to a 'datetime' column caused overflow." error.

My BIRTH_DATE field is set to DATETIME datatype and legnth 8.

Any idea what the problem is?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 17:23:39

Read answer by Michael Valentine Jones.

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

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-05 : 17:33:04

I think it is because I have a few oddball years in my data. I have a few 0's and some 990s... I updated those records to be DOB_YEAR = 1990 and now it works great. Thanks again for all your help and patience!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources