Joining columns - 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

Joining columns

Author

Topic

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-11 : 10:31:41

I have 3 columns with text (dates) 06 03 08. I want to join those columns into one column.

Help, cast or concat does not work.

Lisa Jefferson

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 10:36:46

Are they of integer type?
What is your expected result?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 10:47:18

quote:
Originally posted by l-jeff@excite.com

I have 3 columns with text (dates) 06 03 08. I want to join those columns into one column.

Help, cast or concat does not work.

Lisa Jefferson

did you mean they are of text datatype? if yes, why do you need text fields for storing date values?

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-11 : 10:59:01

I pulled the data for the 3 columns from AS400 server and they are in a table. That table is then pushed to my permanent table where I want to combine the 3 columns into one (As a date). table1 06 03 08 table2 060308

Lisa Jefferson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 11:00:50

SELECT CONVERT(DATETIME, CAST(ColYear) AS CHAR(2)) + CAST(ColMonth) AS CHAR(2)) + CAST(ColDay) AS CHAR(2)), 12)

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 11:04:14

quote:
Originally posted by l-jeff@excite.com

I pulled the data for the 3 columns from AS400 server and they are in a table. That table is then pushed to my permanent table where I want to combine the 3 columns into one (As a date). table1 06 03 08 table2 060308

Lisa Jefferson

Select cast(year_col+month_col+day_col as datetime) from your_table

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 11:04:31

Madhivanan

Failing to plan is Planning to fail

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-11 : 11:13:53

I tried it and I still can't get it to work. Let me show you what does work.

select
cast (OASHIPDTMM + OASHIPDTDD + OASHIPDTYY AS varchar (12))
from
tbl_buffer_openorders

This works, however, I can't insert the new joined column into my other table.

Lisa Jefferson

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 11:17:19

quote:
Originally posted by l-jeff@excite.com

I tried it and I still can't get it to work. Let me show you what does work.

select
cast (OASHIPDTMM + OASHIPDTDD + OASHIPDTYY AS varchar (12))
from
tbl_buffer_openorders

This works, however, I can't insert the new joined column into my other table.

Lisa Jefferson

Did you get any error?

Madhivanan

Failing to plan is Planning to fail

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-11 : 11:20:20

When inserting - Yes. Incorrect syntax near ')'.

select
cast (OASHIPDTMM + OASHIPDTDD + OASHIPDTYY AS char (6)) as readydate
from
tbl_buffer_openorders

insert into tbl_data_orders (readydate)

Lisa Jefferson

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-06-11 : 11:26:03

reverse the order of the insert & select statements

insert into table xyz (col12,2,3,4,5,etc)
select myvalue1,2,3,4,5,etc from abcd

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-11 : 11:34:10

String or binary data would be truncated.
The statement has been terminated.

New error?????

Lisa Jefferson

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 11:38:13

quote:
Originally posted by l-jeff@excite.com

String or binary data would be truncated.
The statement has been terminated.

New error?????

Lisa Jefferson

What is the datatype of the new column?
You need to increase it's size

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 11:41:15

Did you try my suggestion?
Maybe you have an invalid combination of YYMMDD?

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

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-11 : 11:45:02

Thanks, I fixed the size. Now I'm getting this error.

Cannot insert the value NULL into column 'LINE', table 'Shipping.dbo.tbl_Data_Orders'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Lisa Jefferson

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 11:51:06

quote:
Originally posted by l-jeff@excite.com

Thanks, I fixed the size. Now I'm getting this error.

Cannot insert the value NULL into column 'LINE', table 'Shipping.dbo.tbl_Data_Orders'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Lisa Jefferson

Do you have NULL values in any of fields you're concatenating? That will cause result to be NULL unless you've CONCAT NULL YIELDS NULL OFF and will break if trying to insert to a NOT NULL column.

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-11 : 12:04:44

Yes. I have some fields that are NULL values. Is that a problem. How do I fix it?

Lisa Jefferson

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 12:09:38

quote:
Originally posted by l-jeff@excite.com

Yes. I have some fields that are NULL values. Is that a problem. How do I fix it?

Lisa Jefferson

select 
cast (COALESCE(OASHIPDTMM,'') + COALESCE(OASHIPDTDD,'') + COALESCE(OASHIPDTYY,'') AS char (6)) as readydate
from 
tbl_buffer_openorders

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-06-11 : 16:38:03

THANK YOU THANK YOU. It is working.

Lisa Jefferson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources