Few of the questions I receive very frequently. I have collect them in spreadsheet and try to answer them frequently.
How to convert text to integer in SQL?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.
How to use CAST or CONVERT?
SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table
Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
YES.
Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
NO.
How to parse/retrieve only numbers from column values contains alpha-numeric characters?
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String
What are the online references for CAST and CONVERT?
CAST and CONVERT
Reference : Pinal Dave (https://blog.sqlauthority.com)
439 Comments. Leave new
Hello,
I want to execute a query in sql server 2008 to multiply two columns. The two columns has following result,
Column1 Column2
12345 1 year
21356 12 months
The issue is, multiplication of the two columns shall be month wise. i.e. 21356 * 12 = xyz. Need to convert year in months to get the desired results.
Tried using CAST and CONVERT but its not helping.
I get the following error,
“Error converting data type varchar to numeric”
I’ll be grateful if some one can help !
Thanks
You should use int datatype for column2 and store integer values
Try
select column1*replace(column2,’months’,”)*12 from table
Many of the issues above can be fixed by using the following UDF:
CREATE FUNCTION [dbo].[UDF_StringListToNumberList](@NumberList VARCHAR(2000), @Delimeter VARCHAR(1))
RETURNS @NumberTable TABLE (Number INT)
WITH SCHEMABINDING
AS
BEGIN
WHILE CharIndex(@Delimeter,@NumberList) > 0
BEGIN
INSERT INTO @NumberTable SELECT Substring(@NumberList,1,(CharIndex(@Delimeter,@NumberList)-1))
SET @NumberList = Substring(@NumberList, CharIndex(@Delimeter, @NumberList)+1, Len(@NumberList))
END
INSERT INTO @NumberTable SELECT @NumberList
RETURN
END
Hi,
probably dumb question!!!
can anyone help me
how can display datetime (output like 26th April, 2010). it was stored in the table as smalldatetime and when i select i need to display it as alphanumeric date.
Thanks,
If you use front end application, do formation there. Otherwise read about CONVERT function in SQL Server help file.
Hi Gerry,
You can use select CONVERT(VARCHAR(11), GETDATE(),109)
Thanks,
Tejas
SQLYoga.com
Nice Article!!
SELECT CAST(650.0000000 AS decimal(18,4))
output:650.0000
SELECT CAST(755.6666666 AS decimal(18,4))
output:755.6667
Dear sir,
i have a varchar value 3.00 to 99.00. How do i select value as numeric between 4.00 and 6.49
Fozi
You should have used decimal datatype to store data
where col*1.0 between 4.00 and 6.49
hello sir
i have 1 table. i have 6 column.i have 1 col of salary
total col in salary 400. but i want to sum only for 200 col
not 400 .plz sir answer this query
thankyou sir
You need to post some sample data with expected result to help you
Hi, i´m having convert this 0004.80E+12 value to number.
How i do?
select cast(‘0004.80E+12 ‘ as float)
Many, many thanks Pinal Dave, I’ve stopped counting the number of times I would hit a wall in my project, but a quick Cyber-search on revealed that your pages would have the EXACT answer I needed, you are More Than A MVP, Thanks a 10^6th.
I have the following query:
SELECT [Employees].name, [Employees].Employeenumber,
Convert ([Timestamp]-([LoggedIn]/1000)/60))/1440+1,”mm/dd/yy”as [DATE],
FROM Employees INNER JOIN mOpInterval ON [Employees].Loginname1 = mOpInterval.Opname
and I’m trying to convert both timestamp and loggedin time to date … can anyone offer me a way to do this.
As you can see, there is a calculation in this query because everything is written in seconds.
Thank you
Doug
Are you trying to convert seconds to valid dates?
Try
dateadd(second,your_col,0)
Madhivanan,
Are you saying to use dateadd instead of convert? Yes I am trying to convert from seconds to valid dates.
Thank you
Doug
Yes. Use dateadd function
That’s a great solution Annan, thanks a lot.
select ‘client’ + Cast(MAX(Cast(substring(client_id,7,1) as int)) + 1 as varchar) from client
What is the error in this cast query???
What is the error you are getting?
select ‘client’ + Cast(MAX(Cast(substring(client_id,7,1) as int)) + 1 as varchar) from client
this is the query
the datatype of the coloumn client_id is varchar which contains data like client1 client2 till client4.
the error message is – Conversion failed when converting the varchar value ‘t’ to data type int.
Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value ‘t’ to data type int.
this is the error
when i created the project this query was running successfully at that time but then i recreated my databse in sql server 2005,now this is the only query which is not working
Plz gimme a solution asap.
when i run
SELECT CONVERT(DATETIME,’06/25/2009′,103)
then i got an error which is
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
help me to resolve this error
when i run
SELECT CONVERT(DATETIME,’06/25/2009′,103)
if face an error which is
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
pls help me to resolve it
Try this
SELECT CONVERT(DATETIME,’06/25/2009′,101)
Thanks sir its working
Sir,
when I convert Numeric value to date value the date increamented by two days.
when i run
select convert(varchar,convert(datetime,40454),103)as date
output is 05/10/2010 instead of 03/10/2010
please help me to solve the problem.
debasis
select convert(varchar,convert(datetime,40454+2),103)as date
sir,
when i run
select convert(varchar,convert(datetime,40454),103)
output is 05/10/2010 instead of 03/10/2010
please solve my problem.
You need to format the dates in the front end application
hi sir,i m using sql database,i have declared a total rate as a varchar i.e where the value stored as 1L,1.3Cr
.my prblem is that how convert it to int so that i can search it easily through c sharp
You need to use proper decimal or money datatype to store these values. Try this
select case when col like ‘%L’ then replace(col,’L’,”)*100000 end from table
I have a SSIS package (created in BI 9). The data is taken sourced from a database and is loaded to an .xlsx. The numeric data type in DB is not exactly being mapped in excel as numeric, rather its being converted to text in excel. May I know the solution to this if possible.
Thanks in advance.
You need to format EXCEL cell to be of numeric in advance