Posted by helmut woess on 10/07/05 08:50

Am 6 Oct 2005 17:36:46 -0400 schrieb v0lcan0:

> I’m trying to cast and integer type to a varchar and then concatenate
> two varchar and ’01’ and convert it into datetime with the following
> code:
>
> CONVERT(datetime, { fn CONCAT({ fn CONCAT(CAST(PeriodYear AS varchar),
> CAST(PeriodMonth AS varchar)) }, ’01’) }, 112) AS date
>
> It gives an error saying syntax error converting datetime from
> character string.
>
> Any help appreciated!!
>
> thx..

If PeriodMonth is < 10 then the result in your statement would be something
like 2005701, which is an invalid datae indeed. And why concat? A simple
"+" is enough. For example:
select CONVERT(datetime,CAST(PeriodYear AS varchar) +
RIGHT('0' + CAST(PeriodMonth AS varchar),2) + '01', 112) AS date
should do the job, if year and month are valid values.

bye,
Helmut

• Next in forum: Re: SQL for field names from a table
• Prev in forum: Re: OpenRowSet with Excel DENIED
• Thread view: Re: SQL Convert function!!

[Reply to this message]