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
@Imran
convert 8 characters date ( ccyymmdd) into a 9 numeric (mmmddccyy)
(select cast(concat(substr(m1.mem_dob,6,2),
substr(m1.mem_dob,9,2), substr(m1.mem_dob,1,4))
as decimal (9,0)) as numdate),
@bghoh,
Always give sample output so that it is easy for us to understand.
Try this script.
select substring(datename(mm,convert (datetime, ‘2009.01.20’,102)),1,3)+ convert(varchar(2),datename(day,convert (datetime, ‘2009.01.20’,102)))+ convert(varchar(4),datename(year,convert (datetime, ‘2009.01.20’,102)))
Regards,
IM.
This is the simplest method
select replace(convert(varchar(12),getdate(),0),’ ‘,”)
@bghoh
Regarding your post for Syntax error.
Sir… your script is 50 % wrong. Please check syntax for CASE statement on internet.
Keep trying you are almost there.
If you need help let us know.
Points to remember,
insert into (val1, val2, val3 )
select @Val1 — remember there will be no bracket for select here
,@Val2
,case when Val1 = ‘Xvalue’ Then ‘YValue’
when Val2 = ‘SomeValue’ Then ‘Some other value’ end Column_Name_Alias
from
Table_name
Please look for select with case syntax.
Regards,
IM.
Hi,
I have a Problem I got the data like 5,2,6,3 in a Varachar parameter and I want to convert it into Int type variable how i can do it? plz help me… as soon as possible…….
You need to use this logic in the WHERE clause
where ‘,’+@param+’,’ like ‘%,’+cast(col as varchar(10))+’,%’
@Purnima,
Do you have 5 , 2, 6, 3 in a column but in different rows and you want this varchar data convert to int,
select convert( int, column_name) from table_name
If this is not what you want, please post your complete question, with sample input and sample output.
Regards,
IM.
How to write sql query monthname
select datename(month,getdate())
How to convert 8.23 as 08.23?
This is the formation issue. You should do this in front end application
@kumars SELECT DATENAME(mm, CURRENT_TIMESTAMP)
@Milind SELECT ‘0’ + CAST(‘8.23’ AS VARCHAR)
Hi Milind,
If you have specific “0” to append before Number. You can use Replicate function of SQL.
Example:
DECLARE @t as NUMERIC(8,2)
SELECT @t = 08.23
SELECT CAST(REPLICATE(0,5-LEN(@t)) AS VARCHAR(5)) + CAST(@t AS VARCHAR(5))
Here I specify that there should be 5 length. In this case 8.23 has four digit, so this will add one “0” to this number.
You can find it at:
This will fix your problem.
Thanks,
Tejas
You have saved my life so many times, I can pay anything you want. Anything! Thank again.
Sir,
you a saved my skin again.
Come to myanmar we will treat you best.
I have a table with logon time stored as an integer.
I.E
160138 = 16:01:38
I also have logoff time stored as an interger same way as the logon time.
I need a SQL to fine the amount of time this person was logged on and I am having the worst time.
Can you help?
hi Bob and who get this Problem Use this Function and query….
ALTER Function durationtime(@firstColumn int ,@secondColumn int) returns varchar(500)
as
begin
declare @fidurationpt1 varchar(45)
declare @fidurationpt2 varchar(45)
declare @fidurationpt3 varchar(45)
declare @secdurationpt1 varchar(45)
declare @secdurationpt2 varchar(45)
declare @secdurationpt3 varchar(45)
declare @secduration varchar(60)
declare @totduratpd varchar(505)
declare @duratpt1 int
declare @duratpt2 int
declare @duratpt3 int
set @fidurationpt1 =substring(cast(@firstColumn as varchar(45)),1,2)
set @fidurationpt2 =substring(cast(@firstColumn as varchar(45)),3,2)
set @fidurationpt3 =substring(cast(@firstColumn as varchar(45)),5,2)
set @secdurationpt1 =substring(cast(@secondColumn as varchar(45)),1,2)
set @secdurationpt2 =substring(cast(@secondColumn as varchar(45)),3,2)
set @secdurationpt3 =substring(cast(@secondColumn as varchar(45)),5,2)
set @duratpt1 = cast(@secdurationpt1 as int)- cast(@fidurationpt1 as int)
set @duratpt2 = cast(@secdurationpt2 as int)- cast(@fidurationpt2 as int)
set @duratpt3 = cast(@secdurationpt3 as int)- cast(@fidurationpt3 as int)
set @totduratpd =cast(@duratpt1 as varchar(45))+ ‘hrs:’ + Replace(cast(@duratpt2 as varchar(45)),’-‘,”)+ ‘min:’ + replace(cast(@duratpt3 as varchar(45)),’-‘,”)+’sec’
–set @totduratpd = cast(@duratpt1 as varchar(135))
return @totduratpd
End
Use this query
select cast(dbo.durationtime(Logontim,Logofftim)as varchar(50)) as durationtime from serverlogon
Data like this
logon time
160138
130138
120138
120138
logoff time
180348
180348
190348
190137
duration time
2hrs:2min:10sec
5hrs:2min:10sec
7hrs:2min:10sec
7hrs:0min:1sec
Try this
declare @login int, @logoff int
select @login=160138 , @logoff=190325
select
@login as login_time,
@logoff as logoff_time,
convert(varchar(8),cast(stuff(stuff(@logoff,3,0,’:’),6,0,’:’) as datetime)-cast(stuff(stuff(@login,3,0,’:’),6,0,’:’) as datetime),108) as diff
Hello,
I am trying to do a DTS to import excel to sql server 2005 table. I have an amount field that is numeric 8,2 in SS2005k. The excel field is DT R8. How can I get the correct conversion?
It currently translates the field to DT-Numeric but the cents is dropped. It ends up being 25.00 instead of 25.12.
Thank you,
PN
Hi All….
I found the following messege from BOOKS-Online which has been published by Microsoft on November-2008. Please have a look at it and reply to my mail if there is any wrong…
Automatic data type conversion is not supported for the text and image data types. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000 bytes.
-> If you try an incorrect conversion such as trying to convert a character expression that includes letters to an int, SQL Server returns an error message.
Actually I had a requirement that Conversion of some text to an integer value. I used the functions CAST/CONVERT to do this. But I got the error messege like “Conversion failed when converting the varchar value ‘N/A’ to data type int.”
As the error says, how can you convert characters to INT?
Can you post some sample data with expected result?
Hi Friends, i need help. Here i am exporting 4000 records in sql server. this file is fixed file. Here i need convert data into date.
For eg: i have data
19770319 19871002
i need to convert in to as date
1997/03/19 1987/10/02
how i can write a query while data expororting into database.
thanks
Hi Sunil,
Please use convert funtion you can able to find the expected date formats
Declare @Str as Datetime
set @Str = ‘19770319’
select convert(varchar(10),@Str,111) as ExportDate
Hi Sunil
R u trying to export the data from the excel file or flat file?
Thanks to Anand and Gram It Helped me a lot..thank you very much
The query to convert varchar to integer helps
but what we have to do for null values
Thanks and Regards
MaNu GoEl
It depends on what you want to do
If you want to default it to 0, use
coalesce(cast(col as int),0)
Hi,
I want to perform the following steps
1. Convert the real number into a character.
2. Convert the resulting character to a decimal.
3. Round the value at the N+1 th place.
My requirement is
When n=2
I want to round 18.005 to 18.01 or 18.0049 to 18.00(at 100th place)
when i try with
Declare @real real
Set @real=18.005
print Round(Convert(Decimal(15,7),Convert(varchar(30),@real)),2)
i can able to get the result until i get some indefinite real value like -4.8892562E-08. so when i try the same logic, i am getting a conversion error(Error converting data type varchar to numeric.). Please help me to get the problem resolved. thanks in advance
Declare @real real
Set @real=18.005
select round(cast( @real as decimal(12,3)),2)
Hi,
Please imagine this situation.
In a column i have “cx.20” or “cx.5” and in another column i have “10”. My goal is to multiply the first with the second, obviouslly i can only multiply 20 with 10. My doubt is how?I tried so many ways and always this error “error converting data type varchar to numeric”.
Also in the first column i have “Un” without numbers…in that case the multiplying result would be the number in the second column.
Please help me!
Here’s what i tried
([Malaquias$Sales Line].[Quantity]*PARSENAME([Malaquias$Sales Line].[Qty_ per Unit of Measure],1)) as qtdpedida,
sum([Malaquias$Sales Invoice Line].[Quantity]*PARSENAME([Malaquias$Sales Invoice Line].[Qty_ per Unit of Measure],1))as qtdenviada
——————————————————————————
where Quantity is column 2 and and qty_ per unit of measure is column 1