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

Transact-SQL (2005)

Align Right

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-09 : 23:29:28

Hi, how would you align to right without this long boring syntax that i created?

PRINT	CAST('No.' as char(5))+' '+CAST('[Stock Code] Stock Description' as char(45))+'    '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +0, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +0, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +1, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +1, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +2, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +2, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +3, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +3, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +4, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +4, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +5, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +5, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +6, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +6, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +7, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +7, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +8, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +8, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +9, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +9, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +10, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +10, @startdate))) as char(21)))+' '+
		SPACE(21-LEN(DATENAME(MONTH,(DATEADD(MONTH, +11, @startdate))))) + RTRIM(CAST(DATENAME(MONTH,(DATEADD(MONTH, +11, @startdate))) as char(21)))+' '+
		SPACE(21-LEN('Total'))+'Total'

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-09 : 23:54:14

you must have a fixed length to align it

right(space( <the fixed length> ) + <the string>, <the fixed length> )

example

right(space(20) + convert(varchar(10), getdate(), 121), 20)

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 00:11:39

Hehe...thx alot mr.tan...you always helping me ^^...btw i haven't receive yours backup database of sql?

PRINT	CAST('No.' as char(5))+' '+CAST('[Stock Code] Stock Description' as char(45))+'    '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +0, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +1, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +2, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +3, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +4, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +5, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +6, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +7, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +8, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +9, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +10, @startdate))), 21)+' '+
		RIGHT(SPACE(21) + DATENAME(MONTH,(DATEADD(MONTH, +11, @startdate))), 21)+' '+
		SPACE(21-LEN('Total'))+'Total'

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 00:13:46

the database is too huge. . . backup is still in progress . .

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 00:22:12

>"< please be my teacher until i climb up to you head...

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 00:28:18

quote:
Originally posted by waterduck

>"< please be my teacher until i climb up to you head...

Hope can help...but advise to wait pros with confirmation...

I am not that tall . . It will be very easy to do that

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 02:05:02

hehe...don't talk like this...you are one of the elite also lar

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 02:12:15

quote:
Originally posted by waterduck

hehe...don't talk like this...you are one of the elite also lar

Hope can help...but advise to wait pros with confirmation...

I mean more on physically comparing to those from US or Europe

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-10 : 02:16:17

quote:
I mean more on physically comparing to those from US or Europe

But you still might tall..

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources