By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,566 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,566 IT Pros & Developers. It's quick & easy.

SQL Convert function!!

P: n/a
Im 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..

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict261333.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=900084
Oct 6 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
See what you get from

select PeriodYear, PeriodMonth
from yourTable
where PeriodYear not between '1753' and '9999'
or (PeriodMonth not between '1' and '12'
and PeriodMonth not between '01' and '09')

Steve Kass
Drew University

v0lcan0 wrote:
Im 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..

Oct 7 '05 #2

P: n/a
Am 6 Oct 2005 17:36:46 -0400 schrieb v0lcan0:
Im 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
Oct 7 '05 #3

P: n/a
"" wrote:
See what you get from

select PeriodYear, PeriodMonth
from yourTable
where PeriodYear not between '1753' and '9999'
or (PeriodMonth not between '1' and '12'
and PeriodMonth not between '01' and '09')

Steve Kass
Drew University

v0lcan0 wrote:
Im 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..


thx for that steve..

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict261333.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=904391
Oct 13 '05 #4

P: n/a
"" wrote:
Am 6 Oct 2005 17:36:46 -0400 schrieb v0lcan0:
Im 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


thx helmut...being a new SQL programmer, your suggestion is really
worthwhile.

thanks again

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict261333.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=904392
Oct 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.