SQL Column Combine - 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)

SQL Column Combine

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-12 : 13:05:58

I have a month and year column, i want to combine the two so i do the following:

select Month+Year from SnapsRaw as date

problem is, the output has lots of spaces in between the month and year

July 2006

How do i get it so that there is just one space in between

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-09-12 : 13:13:57

You can do some thing like below

declare @month varchar(50)
declare @year varchar(50)
select @month = 'May '
select @year = ' 2007'
select replace(replace(@month+'*'+@year,' ',''),'*',' ')

Thanks
Sanjeev

Sanjeev Shrestha
12/17/1971

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 13:26:58

select datename(month, yourdatetimecolumn) + ' ' + datepart(year, yourdatetimecolumn)
from yourtable

Peter Larsson
Helsingborg, Sweden

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-12 : 13:47:00

I assume i am getting this because I dont have the datetime as the datetype for either column

Syntax error converting datetime from character string.

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-12 : 13:47:35

small correction to earlier query
select datename(month, yourdatetimecolumn) + ' ' + convert(char(4),datepart(year, yourdatetimecolumn))
from yourtable

Added Convert function to avoid error

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-12 : 13:50:05

datename(year, <yourdatetimecolumn>) will return the year as character string

EDIT:
>>I assume i am getting this because I dont have the datetime as the datetype for either column

Yes, what are the datatypes?

Be One with the Optimizer
TG

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-12 : 13:50:57

still get the same message

Syntax error converting datetime from character string.

this is what im doing:

select datename(month, [month]) + ' ' + convert(char(4),datepart(year, [year]))
from snapsraw

month is a char(10) in my table, and year is a char(10)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-12 : 13:55:13

Interesting choice


select	rtrim(month) + ' ' + rtrim(year)
from	(select convert(char(10), 12) [month], convert(char(10), 2006) as [year]) a

output:
--------------------- 
12 2006

Be One with the Optimizer
TG

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-12 : 14:04:47

TG, love the advice, it was something I was struggling with as far as what dataype to make it? I was told by someone else on the forums that I should always seperate the month,year into two different columns, am I better of setting those datatypes to datetime? I just didnt like the time showing up in the datetime datatype.

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-12 : 14:06:53

I think you have both character columns to store Month and Year.
If I am not wrong just use rtrim function it will solve your problem

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-12 : 14:12:35

It depends on what the column values represent. Datetime is by far the most flexible as a single column choice and offers a lot of advantages including the ability to derive any part of the date including year and month. I don't know what you are using these columns for but at the least I should think they should be some flavor of integer. Then you could sort and do math with them without converting. And even if you decided that you need character based data for some reason, you should use varchar or select a length that makes sense for actual data. I would also recommend that you not take presentation factors into account when designing your datamodel. The data can always be presented however you want using whatever tool you are using to present the data to the client.

Be One with the Optimizer
TG

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-12 : 14:43:03

I guess my question would be than, is there a way I can convert 20060801 to August 2006? 20060801 is my primary key, that I want to show up in a dropdown as August 2006

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-12 : 14:50:12

Of course. But what datatype is 20060801? If it's datetime, then you can use the suggestion that Peso sent (but use datename for both month and year parts).

Be One with the Optimizer
TG

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-12 : 15:20:51

Thanks everyone, I appreciate your time and help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources