String/Date Concatenation causes conversion error - streamline fix suggestions

[Chris H]

Hi,
I'm trying to concatenate a Description (nchar(100)) and Date
(datetime) as Description and my initial effort was just
"...description+' '+open_date as description..." which throws a date/
string conversion error; finally came up with a working string below
but don't think it's the optimal way to do this - any suggestions?

select (rtrim(description)+'
'+rtrim(convert(char(2),datepart(mm,open_date)))
+'/'+convert(char(2),datepart(dd,open_date))
+'/'+convert(char(4),datepart(yyyy,open_date))) as description from
oncd_opportunity where opportunity_id=?

open_date is not a required field at the db level, but it is required
on the form so it should not be null as a rule.

[Shiju Samuel]

You can use the third optional parameter of the convert to get the
date in the format you want.

convert(char(20),open_date,101)

-
Shiju

On Sep 4, 7:34 pm, Chris H <[e-mail]>
wrote:

Hi,
I'm trying to concatenate a Description (nchar(100)) and Date
(datetime) as Description and my initial effort was just
"...description+' '+open_date as description..." which throws a date/
string conversion error; finally came up with a working string below
but don't think it's the optimal way to do this - any suggestions?

select (rtrim(description)+'
'+rtrim(convert(char(2),datepart(mm,open_date)))
+'/'+convert(char(2),datepart(dd,open_date))
+'/'+convert(char(4),datepart(yyyy,open_date))) as description from
oncd_opportunity where opportunity_id=?

open_date is not a required field at the db level, but it is required
on the form so it should not be null as a rule.

[Chris H]

Shiju,

Thanks - the working (and streamlined) query becomes:

select (rtrim(description)+' '+convert(char(20),open_date,101)) as
description from oncd_opportunity where opportunity_id=?

Much more elegant - thanks!