question

udhaya avatar image
udhaya asked

Create select query how to convert varchar to int by using cast

I want to convert the values - 2to5experience - 3to7experience to Minexperience: - 2 - 3 **Maxexperience** - 5 - 7 So i create the following query, select cast(substring(Cast(Experience AS VARCHAR(50)),1,1)*365*24*60*60 as int) as Experience from requirementsdetailsfororganization Here the query is working. when i use following one not working. Now i use this query in select statement for insert.. select 'Insert into Jobs(requirementId,organizationId,Minexperience)values(' + Cast(o.RequirementID as varchar(50))+',' + Cast(o.OrganizationID as varchar(50))+',' + cast(substring(CAST(Experience AS VARCHAR(50)),1,1) as int)+')' FROM RequirementsDetailsforOrganization AS o It shows error "Conversion failed when converting the varchar value ')' to data type int" Any idea?
sql-server-2008selectsubstringcast-convert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

udhaya avatar image
udhaya answered
My answer is, select 'Insert into Jobs(requirementId,organizationId,Minexperience)values(' + Cast(o.RequirementID as varchar(50))+',' + Cast(o.OrganizationID as varchar(50))+',' + cast(substring(CAST(Experience AS VARCHAR(50)),1,1) as varchar(50))+')' FROM RequirementsDetailsforOrganization AS o
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
The error is coming from the line cast(substring(CAST(Experience AS VARCHAR(50)),1,1) as int)+')' as you are saying the first expression is an `int`, whereas the second is a character string ')' so SQL thinks you are trying to integer add a number and a string. Try removing the cast to an int: substring(CAST(Experience AS VARCHAR(50)),1,1) +')' You are trying to build up a query string, so even the numbers should be treated as character data.
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ah, you beat me to it.
0 Likes 0 ·
when i use this query it is normally ok. But i want do some arithmetic operations here like substring(CAST(Experience AS VARCHAR(50)),1,1)*365*24*60*60 +')'. when i put like this it occurs same error.
0 Likes 0 ·
So, wait, you're asking for experience in years, but storing it as seconds? Why?
0 Likes 0 ·
I mean, I've got what I would call well over 15 years experience of working with SQL Server - I first worked with it some time in about October 1995, and have been using it on projects ever since; however, there's no way I would consider myself as having half a billion seconds of experience of working with it - for a kick-off, at least 30% of that time I spent sleeping!
0 Likes 0 ·
Oh, and your conversion fails to take into account leap years.
0 Likes 0 ·
Thanks for the effort.I updated my answer. But i get another problem. My values are 0to12years means when i use above code, it shows only0 12y. If i remove that place means it is not showing 0.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Because you're creating a string for execution, you don't want to do the `CAST` around the `SUBSTRING` - in effect the way you're doing this is asking SQL to convert a character to an int before adding it to a string (which requires it to be converted to a character string). Ditch the final CAST().
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.