471,599 Members | 1,563 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,599 software developers and data experts.

SQL Convert function!!

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
4 15604
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
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
"" 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
"" 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.

Similar topics

reply views Thread by Bill | last post: by
1 post views Thread by shank | last post: by
5 posts views Thread by Andy Sutorius | last post: by
1 post views Thread by Ken Hughes | last post: by
reply views Thread by Wes Peters | last post: by
1 post views Thread by Wes Peters | last post: by
2 posts views Thread by XIAOLAOHU | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.