Get rid of spaces in date field

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)

Get rid of spaces in date field

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-14 : 15:31:28

for example for a specific date the below gives the dates as

Should I use RTrim? I am not sure about the syntax

November/2005
April /2005
October /2005
March /2005
May /2005

convert(char(8), datename(mm, InquiryDate)) + '/' + convert(char(4), datepart(yyyy, InquiryDate))

Sachin.Nand

2937 Posts

Posted - 2010-04-14 : 16:06:35

Are those spaces of same length?
select REPLACE('November /2005',' ','')

PBUH

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-04-14 : 17:47:23

[code]select
*,
[Month/Year] =
convert(varchar(20),datename(mm,InquiryDate)+'/'+datename(year,InquiryDate))
from
( -- Test Data
select InquiryDate = getdate() union all
select InquiryDate = getdate()+30 union all
select InquiryDate = getdate()+150
) a[/code]
Results:
[code]InquiryDate Month/Year
------------------------------------------------------ --------------------
2010-04-14 17:45:32.563 April/2010
2010-05-14 17:45:32.563 May/2010
2010-09-11 17:45:32.563 September/2010

(3 row(s) affected)[/code]

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-15 : 03:13:11

quote:
Originally posted by sqlnovice123

convert(varchar(9), datename(mm, InquiryDate)) + '/' + convert(char(4), datepart(yyyy, InquiryDate))

September has 9 characters.

Better to write

DATENAME(MONTH, InquiryDate) + '/' + DATENAME(YEAR, InquiryDate)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources