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?
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
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.
So, wait, you're asking for experience in years, but storing it as seconds? Why?
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!
Oh, and your conversion fails to take into account leap years.
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().
14 People are following this question.
