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 (http://blog.SQLAuthority.com)
Hi,
I have datas like the following and would like to convert them to date format.
1177091222608
1178856000000
How do I do this please. The following is the script that I wrote. I am unable to proceed further.
DECLARE @m bigint, @n int, @k int
SET @m =1178856000000
SET @n = CONVERT(int, @m/1000 + 1900)
SELECT @m, @n
SELECT @k = (@m%1000)
SELECT @k
How to convert varchar variable which may contain ‘aaa’ or ’100′ we want convert when the varchar has ’100′ in a sql statement
Hi Swamy,
Try:
CONVERT(CHAR, @m,103)
The 103 indicates date format dd/mm/yy. 101 would give you American.
Michael
Hi,
how do i convert string type like ‘ 104,116, 96′
as int data type??
i am not able to use cast or convert since…
the string has been separated by commas
remove the Commas than You can Convert Use patindex or charindex…
You may need
where ‘,’+@string+’,’ like ‘%,’+cast(col as varchar(10))+’,%’
@Madhivanan thanks alot
Hi Madhivanan,
Can you please elaborate it in detail
hi,
i have a doubt ,i want to convert months into numbers i.e,
jan as 1,feb as 2 ….is it possible using cast and convert func
declare @month varchar(12)
set @month=’jan’
select month(cast(@month+’ 2000′ as datetime))
Hi,
@aswini,
Visit http://search.SQLAuthority.com and search for list to table.
@jvchoudary,
You can use case statement.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi, I’m importing text data. I have
“24.25-”
“19.00″
etc.
How do you convert text data to numeric with the sign? I need it to look like this.
-24.25
19.00
etc.
I’m using this:
CASE WHEN ISNUMERIC(Hours)= 1 THEN CAST(Hours AS NUMERIC) ELSE 0 END AS Hours
It doesn’t think “24.25-” is numeric so it’s setting it to 0.
thank you.
Hi Judie this code will work for You use this User Defined
alter Function Signs(@Column text)
returns numeric(4,2)
as
Begin
Declare @Count int
Declare @returncolumn varchar(100)
Declare @leng int
Declare @search int
set @Count =Count(*)
while(@Count>0)
Begin
set @returncolumn = cast(@Column as varchar(100))
set @search = patindex(‘%-%’,@Column)
if @search != 0
Begin
set @leng =len(@returncolumn)
set @returncolumn = substring(@returncolumn,1,@search-1)
set @returncolumn = ‘-’+ @returncolumn
set @returncolumn = cast(@returncolumn as numeric(4,2))
set @count =@count-1
End
Else
set @returncolumn = cast(@returncolumn as numeric(4,2))
set @count =@count-1
End
return @returncolumn
End
Before using this Function
24.25-
19.00
after this using function
-24.25
19.00
After
Hi Judie this code will work for You use this User Defined
alter Function Signs(@Column text)
returns numeric(4,2)
as
Begin
Declare @Count int
Declare @returncolumn varchar(100)
Declare @leng int
Declare @search int
set @Count =Count(*)
while(@Count>0)
Begin
set @returncolumn = cast(@Column as varchar(100))
set @search = patindex(‘%-%’,@Column)
if @search != 0
Begin
set @leng =len(@returncolumn)
set @returncolumn = substring(@returncolumn,1,@search-1)
set @returncolumn = ‘-’+ @returncolumn
set @returncolumn = cast(@returncolumn as numeric(4,2))
set @count =@count-1
End
Else
set @returncolumn = cast(@returncolumn as numeric(4,2))
set @count =@count-1
End
return @returncolumn
End
Before
24.25-
19.00
after using this function
-24.25
19.00
After
Always use proper datatype to store data
select case when col like ‘%-’ then left(col,len(col)-1)*-1 else col*1 end from your_table
I have a similar problem as aswini has or had. My query looks like this.
declare @site as varchar(20)
set @site = ’1,2,3,4,5,6,7,8,9′
select count(*) as numbersof from tassmnt a
where cast(a.site as varchar) in (@site)
here site column is integer datatype in sql database. so i changed it to varchar. This query returns “zero” records to me. So what modification has to be done to the above query to get my desired result.
select count(*) as numbersof from tassmnt a
where ‘,’+@site+’,’ like ‘%,’+cast(a.site as varchar(10)) +’,%’
Thanks alot from Nikhil Soni, Amrinder Pandher
can anyone tel me how to generate dynamic reports in SSRS?
What did you mean by it? Do you want to build a report based on the output from a Dynamic sql?
Hi Mithun/aswini,
we cannot compare list of values as string against int. to compare you need to create temp table and split string into rows and use IN clause in SELECT statement.
declare @site as varchar(20)
set @site = ’1,2,3,4,5,6,7,8,9′
declare @t1 table
(
t1 int
)
while charindex(‘,’,@site) > 0
begin
insert into @t1 select substring(@site,1,1)
SET @site = substring(@site,3,len(@site))
end
Select * from table1
where site in ( select * from @t1)
cheers,
anand.
Hi
Hi Anand,
insert into @t1 select substring(@site,1,1)
line will have problem if CSV contains 2 digit no.
i.e ’15,1,2,3′
it will give following error on 15 in csv
“Conversion failed when converting the varchar value ‘,’ to data type int.”
how i convert varchar (6/7) this type into int
How do you want it to be sored in int column?
how do i convert a variable which is INT to varChar? or vice versa.
-considering the varChar variable can only be months of the year which user selects when the stored procedure runs?
I want this detail
Use CAST or CONVERT function
Hi,
I have following scenario and highly appreciated your help.
I have 2 tables table1,table2 with columns table1.Date_opened,table2.date_open,Both the columns are Varchar(25).
I need to check a condition
1)if table1.date_opened=null then table2.date_open should be as ResultDate.
2) The ResultDate is paramater for my query to display the results where ResultDate is between(@fromdate and @Todate,which are of type Datetime.
Thanks in advance,
Rao
use
coalesce(table1.date_opened,table2.date_open)
hi, how do convert the follwing data into single digit? without any decimal places and zeros? thanx…
from “001.000″ to “1″ ?
Most likely you may need
select cast(001.000 as int)
hi;
natarajan u should be used as convertion of floting point expression
select convert(int,(select 8/7))
To avoid the error that CoolDeep encountered when the string contains numbers of varying length, try this change from Anand’s suggestion
declare @site as varchar(2000)
set @site = ’123,234,34567,45678,567890,6789012,701,80,9′
declare @t1 table
(
t1 int
)
while charindex(‘,’,@site) > 0
begin
insert into @t1 select substring(@site,1,(charindex(‘,’,@site)-1))
SET @site = substring(@site,charindex(‘,’,@site)+1,len(@site))
end
insert into @t1
select @site
Select * from @t1
I can’t get sp_executesql to accept more than 10 converted data types. From and after the 11th variable it repeats the converted data type of the 10th.
Alter Function Brack(@Column char(1200))
returns char(800)
as
Begin
Declare @Count int
Declare @returncolumn char(60)
Declare @leng int
set @Count =Count(*)
while(@Count>0)
Begin
set @leng = patindex(‘% %’,@Column)
set @returncolumn =’(‘ + left(@Column,@leng)+ ‘)’ + substring(@column,@leng,len(@column))
set @count =@count-1
End
return @returncolumn
End
Mr. vipul
Declare @Count int // Count No.or Rows to be counted
Declare @returncolumn char(60) //Result store purpose
Declare @leng int //Length will be stored after searching set @Count =Count(*) // rows count
while(@Count>0) //Conditional loop
Begin
set @leng = patindex(‘% %’,@Column) //space position
set @returncolumn =’(‘ + left(@Column,@leng)+ ‘)’ + substring(@column,@leng,len(@column))//will get result
set @count =@count-1//decreament one count
End
return @returncolumn //End of the Result
End
If any doubts or clarrifications please mail me this is a worked out example definitely it will work 100%
Phone Type
489 234-7654 for this type i have worked it My result would be (489) 234-7654.SImilarly you can work with your problem with minor changes
Alter Function Brack(@Column char(1200))
returns char(800)
as
Begin
Declare @Count int
Declare @returncolumn char(60)
Declare @leng int
set @Count =Count(*)
while(@Count>0)
Begin
set @leng = patindex(‘% %’,@Column)
set @returncolumn =’(‘ + left(@Column,@leng)+ ‘)’ + substring(@column,@leng-1,len(@column))
set @count =@count-1
End
return @returncolumn
End.
EX 396 476-7689 i.e (396) 476-7689
hello sir,
this is vipul
i am confused abt one query
so pls help me
i have a column name ‘phone no’
so like 398-768-6789
so i want to convert it to
(398)-768-689
SO PLS DO IT FOR ME
THANKS
Create UserDefined Function for that Using PatIndex or CharIndex.If You want to send the Entire Code U may reply me.I will send
of this Type 408 496-7223
Alter Function Brack(@Column char(1200))
returns char(800)
as
Begin
Declare @Count int
Declare @returncolumn char(60)
Declare @leng int
set @Count =Count(*)
while(@Count>0)
Begin
set @leng = patindex(‘% %’,@Column)
set @returncolumn =’(‘+ left(@Column,@leng-1)+’)’ + substring(@column,@leng,len(@column))
set @count =@count-1
End
return @returncolumn
End
If you use front end application, do formation there
Hi,
Am having a column with varchar type which have some null values in it . I want to convert it to numeric. When i use cast or convert function am getting errror
Thanx in advance
using nvl we can solve this
nvl is for ACCESS. For SQL Server use coalesce or isnull
If the value is NULL, what do you want to see it as a numeric value?
Vipul,
You can use replace function
replace(’398-768-6789′,’398′,’(398)’)
Thank you very much … I did not remember the syntax .. you help me :)
Thanks and bye :)
is there is any sql query that,if i give “1″ as input,the output should be “one”.help me to solve this query
Where do you want to show this?
Some reporting tools like Crystal Reports has this feature
Hi
I have numbers that have been imported from a DB2 system
‘-2.e-002′
How can I convert them into normal numbers for use with decimal datatype?
Many Thanks
select cast(-2.e-002 as decimal(12,2))
Hi Pinal,
I need some help on getting an integer value for a string which could be anything of varchar(90).
The string may have special characters like -/\()*&%@,.
All I need is an integer value consistently the same value everytime.
If I input as ‘ABC’ let’s say value should 100 or ‘ABCD’ output like 1001.
And if input as ‘CBA’ it should be different from ‘ABC’ so vlue will be 150.
Is there any I can achieve this in SQL server either using any system/built in or UDF?
I really appreciate your help in this regard.
Thanks
Refer this example
select data,checksum(data) from
(
select ‘abc’ as data union all
select ‘abcd’ union all
select ‘abc’
) as t
need help I have a column that is numberic stores example:2300.43 and I need to change it to a text output. I used a function but it is returning NULL! any idea? here is my function:
ALTER FUNCTION [dbo].[funcConvertMoneytoChar]
(@amount money
)
RETURNS varchar (200)
BEGIN
DECLARE @Output varchar(200)
IF
@amount is not null
SET @amount = cast(@Output as varchar(200) )
–IF @amount IS NULL
— SET @Output = NULL
ELSE
if
@amount= null SET @Output = NULL
RETURN @Output
END
select ‘$’+ [dbo].[funcConvertMoneytoChar](amount)as amount
from check1
mr.rania,
Alter Function Conversions(@Column money)
returns varchar(800)
as
Begin
Declare @Count int
Declare @returncolumn varchar(60)
set @Count =Count(*)
while(@Count>0)
Begin
set @returncolumn =cast(@Column as varchar(60))
set @count =@count-1
End
return @returncolumn
End
123.4900
123.4900
123.4900
123.4900
NULL
Result
123.49
123.49
123.49
123.49
NULL
The currency formation should be done in the front end application
i @ll,
i have a big problem. i need to convert a Int(11) value to an Int(8) in a Mysql stored procedure. how can i do that?
i become mistakes when i do like this:
DECLARE vSendungs_ID INT(11);
SET vSendungs_ID = CONVERT( INT(8), vSendungs_ID);
thanx for help!
Try this
DECLARE vSendungs_ID INT(11);
SET vSendungs_ID = CAST( vSendungs_ID as INT(8)) ;
But for mysql questions post at http://www.mysql.com
hi all,
m using db2 database,n m havin problem in converting char value to decimal value
ive tried cast(colname as Decimal)
but m gettin “data conversion or data mapping error”
ill thankful for your help in this regard.
Does the char column have any non numeric data?
Also post your questions at DB2 forums
how to allow ‘ (single cotes) in a text box for inserting into an sql db
When inserting data to a table, just double the single quotes. Also refer this to know how single quotes work in SQL Server
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
Hi, Mohan
You can use the sqlcommand parameters to insert data in text box to data base or simply use the string to build your query and then execute that
example:
dim str as string = “insert into table values(‘”& textbox1.text &”‘)
Regards
hi
i have a feild phone as varchar like 98+e00012
i want convert that data into numeric
What is your expected output?
Can you help me translate the following SQL which I can use in Oracle into SQL that will work in SQL Server?
select substr(to_char(r_time),1,10)||’00′ as TIME,n_id as NAMEID, count(*) as COUNT from mytab where r_time between to_date(’2008-01-01 09:00′,’YYYY-MM-DD HH24:MI’) and SYSDATE group by substr(to_char(r_time),1,10)||’00′,n_id
Thanks for your help!
Try this
select substring(cast(r_time as varchar(20)),1,10+’00′ as TIME,n_id as NAMEID, count(*) as COUNT from mytab where r_time >=’2008-01-01 09:00′ and r_time<=getdate() group by substring(cast(r_time as varchar(20)),1,10+'00',n_id
HI
I want to transfer data from excel file to sqlsever 2005 table
What should I use???
Use Dts package to import data from excel sheet.
suhas
Hi I need to chache de format to date only, my field is in format mm/dd/yyyy hh:mm:ss, but i only want mm/dd/yyyy, how can i do this, please help me!!!
Thanks in advance
Gaby
Formation only matters only if you want to show them somewhere. If you use front end application, do formation there
Dear Gabriela,
select convert ( varchar(15), getdate(), 101)
output:
08/29/2008
IN the above query , you just have to change getdate() by the column name which has date….
Hope this helps,
Imran.
Hello,
I need help with converting a number into the string (e.g. 1.00 to be ’1′). I am working with SQL Server 2005..
Please help me!
Thanks in advance,
Amina
select cast(1.00 as int)
Hi Friend(s)
I am Learning SQL i do not know how to create and see the out put can you kindly guide me? to get the output ( i required step by step) small small tags which i can understand easily,
1. how to create
2. how to insert
3. how to select
waiting for your reply.
regards
jo
1. Create table tablename
2.Insert into tablename(fileds)values(@fielsd)
3.select * from tablename
if u any doubt sent message to my gmail.id
I need to convert the number 1 to char ’01′.
How can I do that (not using ’0′+cast(1 as varchar) ! hehehe
tks.
Carlos Barini.
That is formation issue which should be done in a front end application
Hello carlos,
I used this script recently .. works absolutely fine.
SELECT
m.MemberId,
RIGHT(REPLICATE(’0′, 10) + CONVERT(VARCHAR, m.MemberId), 5) AS MemberCode
FROM Member m
original Source: http://sqlblogcasts.com/blogs/repieter/archive/2007/11/15/sql-how-to-fill-up-a-column.aspx
Thanks,
Imran.
Dave,
i have read your blog & the site is very interesting , i got the some basic things about sql server , pivot table, Union all. Good luck
cheers
Poonkodi
Sir what is the difference Between Cast and Convert
Except these two differences
1.formating is posiible in convert but not in cast.
2.cast is a ANSI compliance but Convert is not ANSI compliance.
You may need to read this for more informations about CAST and CONVERT
http://beyondrelational.com/blogs/madhivanan/archive/2010/07/22/cast-vs-convert.aspx
Hey this is great stuff! I hope someone can assist me!
I have a table with one field being varchar(25) and output data like JOBR0001 to JOBR0010.
I would like to run a query so that the next value that shows up would be JOBR0011.
I can only generate the 11 so far but I am unable to get the JOBR as I keep getting the error cannot convert varchar to int: -
SELECT REPLACE((select max(fieldx)
from Table1 WHERE fieldx like ‘JOBR%’),’JOBR’,”)+1
Any advise?
HI BG this function work if the id should be like U mentioned
CREATE Function maxvarchar(@Column varchar(25))
returns varchar(500)
as
Begin
Declare @Count int
Declare @myCount int
Declare @returncolumn varchar(100)
Declare @intpart int
Declare @leng int
Declare @search int
declare @searchlast int
declare @lastwopart varchar(25)
declare @TotalCount int
declare @storelast varchar(25)
declare @resultlen int
declare @searchres int
declare @equalcolumn varchar(25)
set @Count =Count(@Column)
set @TotalCount= 0
set @searchlast=0
while(@Count>0)
Begin
if @Count = @TotalCount
Begin
set @search = patindex(‘%R%’,@Column)
set @returncolumn = substring(@Column,1,@search+2)
set @lastwopart = substring(@Column,@search+1,len(@Column))
set @searchlast = cast(@lastwopart as int)
set @searchlast= @searchlast+1
set @storelast =cast(@searchlast as varchar(25))
set @returncolumn =@returncolumn + @storelast
set @resultlen =len(@returncolumn)
if @resultlen != len(@Column)
Begin
set @searchres = patindex(‘%R%’,@Column)
set @equalcolumn =SUBSTRING(@returncolumn,1,@searchres)
set @equalcolumn =@equalcolumn + ’0′
set @returncolumn = substring(@returncolumn,@searchres+1,len(@Column))
set @returncolumn =@equalcolumn + @returncolumn
End
set @Count =0
End
else
set @TotalCount =@TotalCount +1
End
return @returncolumn
End
data like this
JOBR0001
JOBR0002
JOBR0003
JOBR0013
query : select max(dbo.maxvarchar(Empid)) from BG
after query
JOBR0014
Note the length of id should be equal in all rows.
Hi Bg and one who get this Problem.
The Code which i have given earlier has been lengthy and might not be consistent Now this code will be Perfect and Consistent One.
ALTER Function maxvarchar(@Column varchar(25))
returns varchar(500)
as
Begin
Declare @Count int
Declare @returncolumn varchar(100)
Declare @search int
declare @searchlast int
declare @lastwopart varchar(25)
declare @storelast varchar(25)
declare @searchres int
set @Count =Count(@Column)
set @searchlast=0
while(@Count>0)
Begin
set @search = patindex(‘%R%’,@Column)
set @returncolumn = substring(@Column,1,@search)
set @lastwopart = substring(@Column,@search+1,len(@Column))
set @searchlast = cast(@lastwopart as int)
set @searchlast= @searchlast+01
set @storelast =cast(@searchlast as varchar(25))
set @Count=0
while(len(@storelast)<4)
Begin
set @storelast ='0'+ @storelast
End
set @returncolumn = @returncolumn + @storelast
set @Count=0
End
return @returncolumn
End
data like this
JOBR0250
JOBR0002
JOBR0012
JOBR0312
JOBR4312
JOBR4399
My fuction will return the Out pur like this use this query
JOBR4400
Note:The length should be 8 in all the rows
ALTER Function maxvarchar(@Column varchar(25))
returns varchar(500)
as
Begin
Declare @Count int
Declare @returncolumn varchar(100)
Declare @search int
declare @searchlast int
declare @lastwopart varchar(25)
declare @storelast varchar(25)
declare @searchres int
set @Count =Count(@Column)
set @searchlast=0
while(@Count>0)
Begin
set @search = patindex(‘%R%’,@Column)
set @returncolumn = substring(@Column,1,@search)
set @lastwopart = substring(@Column,@search+1,len(@Column))
set @searchlast = cast(@lastwopart as int)
set @searchlast= @searchlast+01
set @storelast =cast(@searchlast as varchar(25))
set @Count=0
while(len(@storelast)<4)
Begin
set @storelast ='0'+ @storelast
End
set @returncolumn = @returncolumn + @storelast
set @Count=0
End
return @returncolumn
End
data like this
JOBR0250
JOBR0002
JOBR0012
JOBR0312
JOBR4312
JOBR4399
My fuction will return the Out pur like this use this query
JOBR4400
select max(dbo.maxvarchar(Empid)) from BG
reply ur QUERY can solve in Multiple ways the Easiest way is
we can use substring tooo in place of Replace…..
select ‘J0BR’ + cast(max(cast(Replace(Empid,’JOBR’,”)as int)+ 1)as varchar)from MAXV
Data like this
JOBR0250
JOBR0002
JOBR0012
JOBR0312
JOBR4312
JOBR4399
JOBR10000
output like this
J0BR10001
max(fieldx)
should be
max(substring(fieldx,4,len(fieldx))*1)
How do I convert a string of numbers into integer?
Do you want to split the numbers and store them seperately?
You cannot convert character to Integer. Not using CAST and Convert function.
Thanks,
Imran.
gram1138 and Anand,
Thank you very much for the code! I had an idea on how to convert my string of numbers with varying lengths, but I didn’t quite remember all the string manipulation functions and I didn’t have enough time to research it again. But with your examples, I got it working in five minutes. Thanks!
Hello Sir,
The information there on this blog is very useful & helpful thanks for providing such informative infromation.
Sir i have a query in ms-sql server2000
can we convert numeric(18,0) to identity(1,1) which will be primary key ,
since this ID numeric(18,0) is a foreign key in another table so i wanted that this key should be as primary auto incrementing key
whereas i am also unable to do insert into statement for the same
error ocuuring is as : Cannot insert explicit value for identity column in table
Can you please help me out.
As error says, you need to omit identity column when inserting data to the table
Hi,
I want to convert output values for a status cloumn which are in char and I want to be displayed as a number just like below
ex:
status: status
green 1
yellow 2
red 3
Thankx
@Jothi,
you can use CASE function, I wrote a small example…
CREATE TABLE EXAMPLE1 ( STATUS VARCHAR(50))
GO
INSERT INTO EXAMPLE1 (STATUS) SELECT ‘GREEN’ UNION ALL
SELECT ‘YELLOW’ UNION ALL
SELECT ‘RED’
GO
SELECT * FROM EXAMPLE1
GO
SELECT CASE WHEN STATUS =’GREEN’ THEN 1
WHEN STATUS =’YELLOW’ THEN 2
WHEN STATUS = ‘RED’ THEN 3 END STATUS
FROM EXAMPLE1
GO
DROP TABLE EXAMPLE1
GO
Regards,
IM.
Please Tell me from wherei can download sql sever 2005
@Swathi.
You can download SQL Server Express Edition for free from this link.
http://www.microsoft.com/downloads/details.aspx?familyid=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en
You can also try for evaluation copy which is valid for 180 days ( 6 months ).
check this link for more details
http://www.microsoft.com/sqlserver/2005/en/us/trial-software.aspx
Regards
IM
HI,
I am the beginner user of the SQL 2005.
May i know how to write a stored proc.
to convert integer into string without using CAST and CONVERT.
Any advise ?
thanks
Why do you want to do this?
Hi Dave,
i m Asma Qureshi here.
when ever i search any of my query in sql server then i found the solution from your blog
yes its very nice.
Thanks
Regards:
Asma Qureshi
Software Engineer
(ASP.Net2.0/3.0 , SQL server 2005/2008, Ajax)
*************************************
United Arab Emirates
(Dubai)
How can convert integer to time(hh:mm:ss) in sql server 2005. I am getting time as in seconds.
Please help me.
select dateadd(second,12345,0)
I got solution
SELECT CONVERT(varchar, DATEADD(second, 60, 0), 108)
Hi,
I had the following code, which worked perfectly with sql2000. @SerCD is a parameter taken in.
IF ISNULL(LTRIM(RTRIM(@SerCD)), ”) = ”
BEGIN
SELECT @SerCD = NULL
END
select @SerCD = case when @SerCD ‘ALL’ then
@SerCD
ELSE ‘%’
end
in the where clause , i had the following, to limit it be sercd
AND cdrFTS.SerCd LIKE convert(text,@SerCD)
However this does not work with sq2005, has something changed in relation to the convert in 2005?
thanks
mark
It should be
AND (@SerCD is null or cdrFTS.SerCd =@SerCD)
I want my application be localized. For this, I am using the regional settings to determine the appearance of the application. One part of it is the decimal delimiter. It can be either , or . (or other characters).
When I insert numbers into the DB, it is saved correctly (i.e. 4,5). However, when I try to convert it to a string (by using cast or convert) I am always getting the . decimal point (i.e. 4.5). I want to keep the number according to the regional settings, meaning that I am expecting the string to contain 4,5. How can I do it? What am I doing wrong?
Store it in varchar and dont convert it
But better approach is to use proper decimal datatype and do formation in your application
hi i have an issue
i’d like to convert to 3 significal number
ex
1.345 = 1.34
0.23 = .230
how is it possible?
thanks alot
husam
It is totally a formation issue. You should do this in your front end application
Also, what do you want to have for 87234.345?
@Husam
Below is a sample script to achieve your task. Remember one thing, when I created example1 table, datatype for the column is varchar, if in your case its not varchar then you have to convert it to varchar, you just have to use Varchar function, if you need help let us know.
Here goes your sample script.
– Create Example Table
CREATE TABLE EXAMPLE1 ( DECIMALVALUES VARCHAR(5))
– Create example data in our example Table, three different values that might exists,
INSERT INTO EXAMPLE1 VALUES ( ’1.345′)
INSERT INTO EXAMPLE1 VALUES ( ’0.23′)
INSERT INTO EXAMPLE1 VALUES ( ’234′)
– Remember if you data is 4 characters length ex, 2345, you will see output as 234 only, so do a through testing before you use this script in your application.
– Check what you have inserted
SELECT * FROM EXAMPLE1
– ********Main Script**********
SELECT CASE WHEN CHARINDEX(‘.’, DECIMALNUMBERS) = 0
THEN SUBSTRING(DECIMALNUMBERS,1,3)
ELSE DECIMALNUMBERS
END DECIMALNUMBERS
FROM
(
SELECT CASE WHEN SUBSTRING(LEFT (DECIMALVALUES+ REPLICATE (’0′,4) ,5),1,1) = 0
THEN SUBSTRING (LEFT (DECIMALVALUES+ REPLICATE (’0′,4) ,5),2,4)
ELSE SUBSTRING (LEFT (DECIMALVALUES+ REPLICATE (’0′,4) ,5),1,4)
END DECIMALNUMBERS
FROM EXAMPLE1
)X
Let us know if you need more help on this.
Regards,
IM.
Hello,
I need to convert 8 characters date ( ccyymmdd) into a 9 numeric (mmmddccyy)
example: 20090120 to be presented as 1202009
ccyymmdd mmmddccyy
Please help
Thank you
You should always use a proper DATETIME datatype to store date values
Try this
select replace(convert(varchar(10),cast(cast(20090120 as char(8)) as datetime),101),’/',”)*1
@bghoh
select convert(varchar(2),datepart(month,convert (datetime, ’2009.01.20′,102)))+ convert(varchar(2),datename(day,convert (datetime, ’2009.01.20′,102)))+ convert(varchar(4),datename(year,convert (datetime, ’2009.01.20′,102)))
If some one has a better way of doing this, please post it here.
Regards
IM.
I have posted the code
See my previous reply
I am posting here too
select replace(convert(varchar(10),cast(cast(20090120 as char(8)) as datetime),101),’/',”)*1
Hi!
Can any one let me know what is the difference between cast and convert()?
Also between varchar and nvarchar
please also mail me on my email id
thanks n regards
These informations are availabe at SQL Server help file
I need help with converting a number into the string (e.g. 100 to be ‘hundred’). I am working with SQL Server 2005..
@Imran
Error in the SQL syntax for date conversion, not sure what is wrong. Please help . Thank you
insert into b0131 (ssn,lname,fname,relcode,sex,birthday,
othinscode,dateofdeath,marstatus,note,effdate, termdate)
(select m1.mem_altid,m1.mem_lname,m1.mem_fname,
(select case
when m1.mem_rel = ’01′ then ‘M’
when m1.mem_rel = ’02′ then
case
when m1.mem_sex = ‘M’ then ‘H’
when m1. mem_sex = ‘F’ then ‘W’
end
when m1.mem_rel = ’03′ then
case
when m1.mem_sex = ‘M’ then ‘S’
when m1. mem_sex = ‘F’ then ‘D’
end
when m1.mem_rel = ’04′ then
case
when m1.mem_sex = ‘M’ then ‘S’
when m1. mem_sex = ‘F’ then ‘D’
end
when m1.mem_rel = ’05′ then
case
when m1.mem_sex = ‘M’ then ‘X’
when m1. mem_sex = ‘F’ then ‘Y’
end
when m1.mem_rel = ’13′ then ‘G’
when m1.mem_rel = ’20′ then ‘P’
else ‘O’
end),
m1.mem_sex,
(select convert(varchar(2),datepart(month,convert
(datetime, m1.mem_dob.102)))+ convert(varchar(2),
datename(day,convert (datetime, m1.mem_dob,102)))+
convert(varchar(4),datename(year,convert (datetime,
m1.mem_dob,102)))),
m1.mem_cob, m1.mem_dod,m1.mem_mar,m1.mem_note1,m1.mem_eff,m1.mem_trm
from mem m1
where m1.mem_lev3 = ‘B0133′);
@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: http://tejasnshah.wordpress.com/2009/02/26/sql-server-replicate-function/
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
i have an integer 24036 in mssql 2000, i need to convert it to month and year.(no date).
Please give me the solution
What is your expected result?
Hi,
I have used to this query,.
declare @v varchar(50)
set @v=’-10851776011.372′
select cast(cast(@v as float) as DECIMAL(20,14)) as Value1
The error it will come like,
Arithmetic overflow error converting float to data type numeric.
How can i convert this type of values,,
Help me please…
@Maniraj
DECIMAL(20, 14) allows for 6 digits to the left of the decimal point. Your number has 11. Hence the overflow.
Perhaps you mean to use DECIMAL(20, 6), which allows 14 to the left and only 6 on the right.
Hi All,
I have a table with column chapterid as int
stored procedure
@chapterid int,
SELECT * FROM Chapters WHERE Chapter IN (@chapterid)
@chapter has multiple values like @chapterid = ’5,6,7,8′
this gives me cast error
how do I convert this varchar to an int? A simple convert or cast statement doesn’t work. Any help would be much
appreciated.
Search for Array+SQL Server in google. You will get to know lot of methods
@Shalini
To search for a list like that, you can use Dynamic SQL.
sir i have a question:
i hav given a single number say 138 ok
and i have to display only 8 which is largest in this given number using sql
plz help me as early as possible
@ammupriya
WITH
DATA(Datum)
AS
(SELECT 138),
Separated(Datum, i, Amount, Digit)
AS
(
SELECT
Datum,
1,
LEN(Datum),
SUBSTRING(CAST(Datum AS VARCHAR), 1, 1)
FROM
Data
UNION ALL
SELECT
Datum,
i + 1,
Amount,
SUBSTRING(CAST(Datum AS VARCHAR), i, 1)
FROM
Separated
WHERE
i <= Amount
)
SELECT
MAX(Digit)
FROM
Separated;
hi how to select only month from string format like (01/06/2009) to June pls help..
Hello Parthee,
Use the below statement:
SELECT DATENAME(MONTH,CONVERT(DATETIME,’01/06/2009′,103))
Kind Regards,
Pinal Dave
Hi everyone, I have a question on sorting a varchar field in numeric order. It is a database we use for engineers, so usually it contains numbers, but sometimes it contains characters as well, and it explains why we used varchar to start with
Say I have a varchar field “frequency” with these data:
1.0
3
4.5
-
100
35
So if I simply do “order by frequency”, we’ll get
-
1.0
100
3
35
4.5
While the correct order should be
-
1.0
3
4.5
35
100
I know I can probably use CONVERT, but it will error out at the dash value “-”. Any suggestions?
Thanks!
Justin
Use this Function
Hi Justin and who get this problem…….
alter function sort(@Column varchar(30)) returns varchar(30)
as
Begin
Declare @Sfindex int
Declare @Beginse varchar(35)
set @Sfindex = patindex(‘%.%’,@Column)
if @Sfindex = 0
Begin
set @Beginse =@Column +’.0′
End
else
set @Beginse =@Column
return @Beginse
end
Use this query…….
select sotcolumn from sortdata order by cast(Replace(dbo.sort(sotcolumn),’.',”) as int) asc
Data.
1.0
3
4.5
-
100
35
Out put
-
1.0
3
4.5
35
100
Try this
select data from
(
select cast(’1.0′ as varchar(10)) as data union all
select ’3′ union all
select ’4.5′ union all
select ‘-’ union all
select ’100′ union all
select ’35′
) as t
where data not like ‘%[^0-9.]%’
order by cast(data as numeric(12,2))
Hello Justin,
You can do it by using CTE like below:
with cte (frequency,frqOrderBy)
as
(select frequency, case isnumeric(frequency) when 1 then convert(money, frequency) else 0 end as frqOrderBy from tblTable )
select frequency from cte order by frqOrderBy
Kind Regards,
Pinal Dave
Thanks! I’ll give it a try.
Justin
I have a problem to export data from excel using opendatasource. I have one column in excel that contains both integers in some rows & text in some rows . When i start importing them text data comes as it is but records containing integer data are being imported but with null values into that column….
Any help will be highly appreciable….
Format the cell to have character data
Try using nvarchar column in the table
Hi, I have currency conversions which are converting from data type money to numeric(38,6) which is in turn throwing an error in my BI tool. I know how to solve that problem, but along the way I became interested to be able to determine the number of digits in stored value. I was actually curious to do some analysis to see if there was a pattern to the currency conversions so I might could change the scale when loaded certain currencies. Thank You
Pinal,
I just wanted to say thanks. I find your blog to be very useful and infomative.
Dan
I want to convert a 4-digit number to dateTime SQL Server 2005. i.e. 9325, the first number is year, so it is 2009, and the rest of number is days of year, so it will be 325th days of 365. How do I do that?
Thank you.
@Lydia
The year is the thousands + 100 (because date 0 in SQL Server is year 1900). To get the thousands, divide by 1000.
The day of year is the remainder of that same division, so % 1000 – 1.
Put those together in DATEADD and you have a date.
WITH Data(X) AS (SELECT 9325)
SELECT DATEADD(y, X % 1000 – 1, DATEADD(yy, 100 + X / 1000, 0)) FROM Data;
Anoher method
declare @date int
select @date=9325
select dateadd(day,@date%1000-1,dateadd(year,2000+@date/1000-1900,0))
Thank you VERY MUCH !!! It works VERY WELL !!!
Hi,
I have a view where I used to convert a numeric column to numeric(32,9). But now I have to convert the same column to numeric but the decimal places aer not fixed but should be taken from a column in table.
Please let me know if there is any way I can use a column or a variable in the numeric() function something like convert(numeric(32,table.dec_places). Please help.
There is no way other than using a dynamic sql
Hello,
I have a table with 5000 IDs. from ID 1-5000. I want to change the ID 1 to 2500, for ex. 1=2500, 2= 2501, 3= 2502….etc.
Hello Subhendu,
At first you will have to remove the IDENTITY property from ID column, then update the ID values and then again add the IDENTITY property.
Regards,
Pinal Dave
Hi Subhendu,
1. If column is IDENTITY then remove IDENTITY for time being.
2. Update data as:
UPDATE Table
SET ID = ID + 2499 –(As you need to start from 2500)
3. If column was IDENTITY column, then mark as IDENTITY now.
Thanks,
Tejas
SQLYoga.com
I have a problem to convert from varchar to Numeric.
Select
Convert(Numeric,ltrim(rtrim(Replace(Replace(BankAccountNo,Char(13),”),Char(10),”)))) As AccNo
From
ReceiptPayment
Where
isnumeric(ltrim(rtrim(Replace(Replace(BankAccountNo,Char(13),”),Char(10),”))))=1 And
BankAccountNo Is Not NULL
And BankAccountNo ”
is my query. I am getting ‘Arithmetic overflow error converting varchar to data type numeric’ (retrieved only partial records with error).
But If I retrieve all records by placing ‘*’ instead of convert statement. What will be the solution?
hi, i have a column that it must be numeric because i use the function MAX somewhere with it , for example,
ADOQuery1->SQL->Text =”SELECT MAX (code) AS NextCode FROM product “;
but i use it also sumewhere else and i compare it with an EditBox text something like this for example
ADOQuery1->SQL->Text=
“SELECT ProductName FROM product WHERE product.code= ‘”+Edit1->Text+”‘ ” ;
in the second example it throws a mistake because i compare an integer with a string i tried this
ADOQuery1->SQL->Text=
“SELECT ProductName FROM product WHERE product.code= ‘StrToInt(Edit1->Text)’ ” ;
but it dosen’t seem to work can you telm if what i am trying to do can be done with CAST or CONVERT and how??????
(sry for my bad english)
i thing it must be something like this for example
“SELECT MAX ( SELECT CONVERT(INT, code) FROM customer) AS PLITHOS FROM customer “;
Hello Xristina,
It seems you are writing the below query in .Net editor:
ADOQuery1->SQL->Text=
“SELECT ProductName FROM product WHERE product.code= ‘StrToInt(Edit1->Text)’ ” ;
Form this query remove the single quote and write as below:
ADOQuery1->SQL->Text=
“SELECT ProductName FROM product WHERE product.code= ” + StrToInt(Edit1->Text) ;
Regards
Pinal Dave
thnks a lot pinal it works perfect…..
Hello.
Can you please tell me the following?
I Have text like ’16pages brochure 4colours’ and in need to convert this text to a unique Alphanumeric numeric Code
Thank you in advanced
Can you show us your expected result?
Give me the output data Nick
Sorry i mean
a unique Alphanumeric OR numeric Code
For the above sample data what is your expected result?
I am using Sql 2005
I have a .xls sheet of nubmers (6000+ of them) and they are straight text.
I ahve tried the phone convert but can’t seem to get it to work.
I need to pull them formatted into a varchar(256) column. they come like this:
000000000000000000000000000000000000
and I need them inserted into the table like this:
00000000-0000-0000-0000-0000-000000000000
They are always the same legnth and they will only have ‘-’ between them.
Thanks,
Jr
Thanks for the help.
I’m hoping you can help me.
I have the following expression:
Select power(power(10.000000000,3.03259784139734),28664879.2888658)
I have tried to execute this expression in SqlServer 2005 but I still get the error message in below.
Arithmetic overflow error converting expression to data type float.
Do you have any idea what I’m doing wrong?
@Thiru
The resultant number is too high.
For example, try: SELECT POWER(1000, 4)
The return is INT< and that is too high for INT, so it produces an error.
Change it to: SELECT POWER(1000.0, 4)
and it works, as the return type is FLOAT.
But the same thing happens with FLOAT, if the number is too high, it will also produce an error.
Works: SELECT POWER(1000.0, 12)
Does not work: SELECT POWER(1000.0, 13)
can any one tel me difference between convert and cast
@chaitanya
CONVERT does more than CAST. CAST is an old standard, so many people like it.
http://msdn.microsoft.com/en-us/library/ms187928.aspx
hi swami,
i am building a database and in a table my primarykey is of the type char(4) and my need is to autoincrement it and i wish to have the details like ‘E001′ and then increment it. is it possible?
Regards,
Anu
Probably easiest way is to write procedure that does inserting.
1. Make sure procedure is ran inside transaction or start one
2. Get the largest PK value from the table (or store it somewhere else)
3. Insert a new row with largest value + 1
4. Commit transaction if you started one in the first step
yes its possible I have done it BG just see the code and make necessary arrangements…
Hello Sir
I am shredding some xml into relational SQL 2005 and the source xml has date time fields in strings of ISO 8601 format. I wish to change these to SQL DateTime fields during shred. I have tried things like
SELECT ID, CAST( LASTMODTIME AS DATETIME)
FROM PROFILES
but the conversion fails.
Hi Andy,
Can YOU Brief out with representation of Data…….. that means how you xml data
Hi everyone, I have a question on sorting a varchar field in numeric order. It is a database we use for testcases.
Say I have a varchar field “reference” with these data:
1.1.1
1.10.1
4.11.1
4.12.1
1.2.1
1.3.1
So if I simply do “order by reference”, i’ll get:
1.1.1
1.10.1
1.11.1
1.2.1
1.3.1
While the correct order should be
1.1.1
1.2.1
1.3.1
1.4.1
1.5.1
1.6.1
1.7.1
1.8.1
1.9.1
1.10.1
4.11.1
4.12.1
I know I can probably use CONVERT, but it will error out at the dot value “.”.
Any suggestions?
Conversion failed when converting the nvarchar value ’1.1.1′ to data type int.
Thanks!
Alex
Here’s one way to do it.
– CTE to split version number field into parts
– How I would love to have regular expressions!
WITH CTE(Field1, Field2, Field3)
AS
(
SELECT
LEFT(Reference, PATINDEX(‘%.%’, Reference) – 1),
SUBSTRING(Reference, PATINDEX(‘%.%’, Reference) + 1, PATINDEX(‘%.[0-9]‘, Reference) – PATINDEX(‘%.%’, Reference) – 1),
SUBSTRING(Reference, PATINDEX(‘%.[0-9]‘, Reference) + 1, LEN(Reference))
FROM
MyTable
)
SELECT
– Concatenate fields back to single line
Field1 + ‘.’ + Field2 + ‘.’ + Field3
FROM CTE
– Fields must be cast to integer before ordering them
– Otherwise DB performs natural sort which doesn’t
– work very well in this case
ORDER BY CAST(Field1 AS INT), CAST(Field2 AS INT), CAST(Field3 AS INT)
@Alex
If none of the sub-parts ever have leading zeroes, just replace the dots, add a 1, and cast as a number.
replace the dots, so there is one big number.
add a one, to keep any final 0s
cast as a number to have the order work:
WITH
Data(Reference)
AS
(
SELECT ’1.1.1′ UNION ALL
SELECT ’1.10.1′ UNION ALL
SELECT ’4.11.1′ UNION ALL
SELECT ’4.12.1′ UNION ALL
SELECT ’1.2.1′ UNION ALL
SELECT ’1.3.1′
)
SELECT
Reference
FROM
Data
ORDER BY
CAST(REPLACE(Reference, ‘.’, ”) + ’1′ AS BIGINT);
Ideally, a three-part code like this should be in three different COLUMNs. That would remove any such issues as the disparate parts are kept separately.
Use this query
select pointdt from pointsrt order by cast(Replace(pointdt,’.',”) as int) asc
Data Like this
1.1.1
1.10.1
4.11.1
4.12.1
1.2.1
1.3.1
Out put like this
1.1.1
1.2.1
1.3.1
1.10.1
4.11.1
4.12.1
hi sir,
this site is very useful for me..
i have 1 doubt.can u pls clear my doubt.
i have money datatype values like
price=650.0000000
price=755.6666666
but i want 2 display like
price=650.0000
price=755.6667
i tried with round(price,4)..
but it doesnt work..
pls help me sir,
regards,
pushpa.
CAST as MONEY might work for you. Try this:
SELECT CAST(650.0000000 AS MONEY), CAST(755.6666666 AS MONEY)
SELECT CAST(650.0000000 AS decimal(18,4))
SELECT CAST(755.6666666 AS decimal(18,4))
hi dave,
thanks for ur reply…
for the last 4 months i m learning sql..i learned most of the topics in theoretical part.
but i want to practice all the topics through websites..can u please suggest any website to practice sql queries..
i m looking the website which will have table structures and query with answers..i m using toad tool for practice the sql queries.
once again thanks ….
Regards,
pushpa
I need the code to work out a persons age from his given birthdate till the current (maby sysdatetime()).
Im trying convertion from date to int, but it giving me trouble.
How would I do this?
Hello Minnaar,
Use the DATEDIFF function to get the difference in years, months or days.
Regards,
Pinal Dave
HI All,
I have one sales column in that vales are like this
200
345.00
56.00
667
5
At present my sales column datatype is nvarchar
and i wanted my sales column data should be in the below format can u please help me out …
$200
$345.00
$56.00
$667
$5
Thanks& Regards,
Dileep.v
Sorry i won’t think it as Big problem But if ur not able to get this i may think ur fresher. r fooling some one………
select ‘$’+ moneys from Dileep
Data like this
200
345.00
56.00
667.00
5.00
Out put data like this
$200
$345.00
$56.00
$667.00
$5.00
Sorry i won’t think it was a Big problem But if ur not able to get this i may think ur fresher. r fooling some one………
select ‘$’+ moneys from Dileep
Data like this
200
345.00
56.00
667.00
5.00
Out put data like this
$200
$345.00
$56.00
$667.00
$5.00
Hi Bob and others Use this query But restrictions should be done By you .
ALTER Function durationtime(@firstColumn varchar(45) ,@secondColumn as varchar(45)) 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
declare @firslen int
declare @seclen int
set @firslen =len(@firstColumn)
set @seclen = len(@secondColumn)
if @firslen < 6
Begin
set @firstcolumn ='0' + @firstColumn
set @fidurationpt1 =substring(@firstColumn,1,2)
set @fidurationpt2 =substring(@firstColumn,3,2)
set @fidurationpt3 =substring(@firstColumn,5,2)
End
Else
set @firstcolumn = @firstColumn
set @fidurationpt1 =substring(@firstColumn,1,2)
set @fidurationpt2 =substring(@firstColumn,3,2)
set @fidurationpt3 =substring(@firstColumn,5,2)
if @seclen < 6
Begin
set @secondColumn ='0' + @secondColumn
set @secdurationpt1 =substring(@secondColumn,1,2)
set @secdurationpt2 =substring(@secondColumn,3,2)
set @secdurationpt3 =substring(@secondColumn,5,2)
End
Else
set @secondColumn = @secondColumn
set @secdurationpt1 =substring(@secondColumn,1,2)
set @secdurationpt2 =substring(@secondColumn,3,2)
set @secdurationpt3 =substring(@secondColumn,5,2)
set @secdurationpt1 =substring(@secondColumn,1,2)
set @secdurationpt2 =substring(@secondColumn,3,2)
set @secdurationpt3 =substring(@secondColumn,5,2)
set @duratpt1 = cast(@secdurationpt1 as int)- cast(@fidurationpt1 as int)
set @duratpt2 = cast(@secdurationpt2 as int)- cast(@fidurationpt2 as int)
if @duratpt2 < 0
Begin
set @duratpt1 = @duratpt1-1
set @duratpt2 = 60 – @fidurationpt2 + @secdurationpt2
End
else
set @duratpt2=@duratpt2
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
insert into serverlogon(Logontim,Logofftim)values(153500,173000)
select dbo.durationtime(cast(Logontim as varchar(45)),cast(Logofftim as varchar(45))) as durationtime from serverlogon
Logon
160138
130138
120138
120138
201500
211500
81500
163000
153500
Logoff
180348
180348
190348
190137
221000
221000
91000
201000
173000
Out put
2hrs:2min:10sec
5hrs:2min:10sec
7hrs:2min:10sec
7hrs:0min:1sec
1hrs:55min:0sec
0hrs:55min:0sec
0hrs:55min:0sec
3hrs:40min:0sec
1hrs:55min:0sec
Put validations as Logon time always less than Logoff time….either by creating rule or in this code
ALTER Function durationtime(@firstColumn varchar(45) ,@secondColumn as varchar(45)) 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
declare @firslen int
declare @seclen int
set @firslen =len(@firstColumn)
set @seclen = len(@secondColumn)
if @firslen < 6
Begin
set @firstcolumn ='0' + @firstColumn
set @fidurationpt1 =substring(@firstColumn,1,2)
set @fidurationpt2 =substring(@firstColumn,3,2)
set @fidurationpt3 =substring(@firstColumn,5,2)
End
Else
set @firstcolumn = @firstColumn
set @fidurationpt1 =substring(@firstColumn,1,2)
set @fidurationpt2 =substring(@firstColumn,3,2)
set @fidurationpt3 =substring(@firstColumn,5,2)
if @seclen < 6
Begin
set @secondColumn ='0' + @secondColumn
set @secdurationpt1 =substring(@secondColumn,1,2)
set @secdurationpt2 =substring(@secondColumn,3,2)
set @secdurationpt3 =substring(@secondColumn,5,2)
End
Else
set @secondColumn = @secondColumn
set @secdurationpt1 =substring(@secondColumn,1,2)
set @secdurationpt2 =substring(@secondColumn,3,2)
set @secdurationpt3 =substring(@secondColumn,5,2)
set @duratpt1 = cast(@secdurationpt1 as int)- cast(@fidurationpt1 as int)
set @duratpt2 = cast(@secdurationpt2 as int)- cast(@fidurationpt2 as int)
if @duratpt2 < 0
Begin
set @duratpt1 = @duratpt1-1
set @duratpt2 = 60 – @fidurationpt2 + @secdurationpt2
End
else
set @duratpt2=@duratpt2
set @duratpt3 = cast(@secdurationpt3 as int)- cast(@fidurationpt3 as int)
if @duratpt3 < 0
Begin
set @duratpt2 = @duratpt2-1
set @duratpt3 = 60 – @fidurationpt3 + @secdurationpt3
End
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
select dbo.durationtime(cast(Logontim as varchar(45)),cast(Logofftim as varchar(45))) as durationtime from serverlogon
Logon
160138
130138
120138
120138
201500
211500
81500
163000
153500
153539
Logoff
180348
180348
190348
190137
221000
221000
91000
201000
173000
163438
Duration Time
2hrs:2min:10sec
5hrs:2min:10sec
7hrs:2min:10sec
7hrs:1min:59sec
1hrs:55min:0sec
0hrs:55min:0sec
0hrs:55min:0sec
3hrs:40min:0sec
1hrs:55min:0sec
0hrs:58min:59sec
Hi,
I have a question. what i have to do when i want a string in ’00001′ format when this is a auto-generated code?
I want to select the max(id) from the table and format the int to my required format like ’00001′?
Please help. Am waiting.
Hi sinha can u brief You want i think that you need to generate Increament which is in string type.is that your question Suppose you say ’000001′ you need auto increament and you query it ’000002′ should be produced.is that ur question
Hi sinha can u brief what You want. i think you need to generate an Increament value which is of string type is that your question? Suppose you say ‘000001′ you need to auto increament (i.e you query it ‘000002′) should be produced.Is n’t it?
Have an identity column to generate number
Have a computed colum as right(’000000′+cast(id_col as varchar(10)),6)
Hello
Can somebody kindly help me with this?
I have a number stored in a table as below:
100258
When i am using the function substr(100258, 2,) and export to a CSV file, i get the output as 2 but i desire to get the output as 002.
@Raman
It isn’t so clear what you want. Try this:
SELECT ’00′ + SUBSTRING(CAST(100258 AS VARCHAR), 4, 1)
I am getting following error
Conversion failed when converting the varchar value ‘Null’ to data type int
when I try to run following query
SELECT DISTINCT
CommTypeEID=CONVERT(VARCHAR(50),
dbo.SalesChannels.UserLevel)+’;'+U1.EID+’;'+CASE WHEN U1.EID IN (‘E023456′,’E04342′,’E07778′,’E045454′,’E068660′,’E110218′,’E074466′,’E079211′,’E014307′,’E045044′,’E081162′) THEN ’17′ ELSE CONVERT(VARCHAR(50), dbo.CommissionPlan.CommissionPlanID) END,
U1.LastName + ‘, ‘ + U1.FirstName + ‘ (‘+ U1.EID + ‘;’ + dbo.CommissionPlan.Description+’)’ AS [AgentName],
U1.lastname, U1.firstname
FROM dbo.mydatabase
Please help me
Im going nuts with this, and I know I more info may need to be provided to answer this correctly, so bear with me. Whoever made the db I’m working with, has money being stored into the db as char(8). For example, the amount $1.39, looks like 0000139 in the table. How can I put the decimal back into the right spot?
I’ve tried many variations of convert/cast and can;t seem to get it right. I need to build a script that does this, please help?
I also would need to drop any leading zero’s if applicable.
For example, an amount of $1,000.00, is showing up in the db as 0100000. Juts for more info, the name of the column is xx_amount, and the data type was made as char(8). Thanks so much.
I had some spare time so I wrote a little function. Try this and see if it works for you.
CREATE FUNCTION StringToMoney(@str VARCHAR(MAX), @DecSep CHAR(1), @ThousandSep CHAR(1))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @ret VARCHAR(MAX)
– Take out leading zeroes
DECLARE @value VARCHAR(MAX)
SET @value = SUBSTRING(@str, PATINDEX(‘%[^0]%’, @str), LEN(@str))
– Take decimals
DECLARE @decimals VARCHAR(2)
SET @decimals = SUBSTRING(@value, LEN(@value) – 1, LEN(@value))
SET @decimals = @decimals + REPLICATE(’0′, 2 – LEN(@decimals))
– Take characters before decimal (what are those called anyway?)
SET @value = SUBSTRING(@value, 0, LEN(@value) – 1)
IF LEN(@value) = 0 SET @value = ’0′
– Insert thousand separators (this would be eeeeaaasy with regexps)
DECLARE @value2 VARCHAR(MAX)
SET @value2 = ”
WHILE LEN(@value) > 3
BEGIN
SET @value2 = CASE WHEN LEN(@ThousandSep) = 0 THEN ” ELSE @ThousandSep END + SUBSTRING(@value, LEN(@value) – 2, LEN(@value)) + @value2
SET @value = SUBSTRING(@value, 0, LEN(@value) – 2)
END
SET @value2 = @value + @value2
SET @ret = @value2 + @DecSep + @decimals
RETURN @ret
END
Is there system Tables/views/sps/fucntions to list the all date time format styles which is an input for convert function?
Read about Convert function in SQL Server help file
It lists out all formats
Thanks for ur immediate reply.
i want to know the style at rum time.if i give the format ‘yyyy-mm-dd’ at run time,i should get the style.is it possbile?(i dont want to hardcode the style)
Why do you want to do this?
If you use front end application, use the format function there
Sir,
Sql parameter p;
Sql connection cn=new sqlconnection(” “);
sql command cmd= new sqlcommand(…);
cmd.CommandType=CommandType.Storedprocedure;
p = new SqlParameter(“@Firstname”, SqlDbType.VarChar);
p.Value = Convert.ToString(txtFName.Text);
Can i pass this p value to Int EmpId?
How can i pass it?
Why dont you try and see if it works?
Hi,
I have a column of type decimal.I want to convert it into foramt HHMMSS,
If i use Convert(datetime,field,8) ,and the field contains 42 then the o/p is 1900-01-01 00:00:00,but i want it as 00:00:42.
Could anyone please tell me what i am missing here?Is it possible to do this conversion without using DATEADD?
select convert(varchar(8),dateadd(second,42,0),108)
Hi,
I have a column of type decimal.I want to convert it into foramt HHMMSS,
If i use Convert(datetime,field,8) ,and the field contains 42 then the o/p is 1900-02-12 00:00:00,but i want it as 00:00:42.
Could anyone please tell me what i am missing here?Is it possible to do this conversion without using DATEADD?
select convert(varchar(10),dateadd(second,42,0),108)
Thanks,But is it possible without using DATEADD?
@chaitra
Perhaps something like this will work:
SELECT CONVERT(DATETIME, ’12:00:’ + ’42′, 14)
Why?
select convert(varchar(10),cast(’00:00:’+’42′ as datetime),108)
hi.
i have data(double) from table then i want convert into format ###,###,###,###.##
my data :1234567890.25
how convert into : 1,234,567,890.25
tq
This is the formation issue you should do this in your front end application
Hi All,
I need help in SQL server 2008 query.
Table structure is
ID Site Linked ID
1 100 2,3,4
2 200 4,5,6
5 100
On a search form, three fields are available. Those are id, Linked_Id, site.
1) On search form , if I select linked_id option and site = 100 then returned records should contain all records which belong to site ‘100’ and records which are linked to those ids. In this case, return records should be having ids 1,2,3,4,5.
2) If I give value as ‘1’ in id field on search form and select linked_id option and site = 100 then in this case it should return records having ids 1,2,3,4.
Please help me in forming this query.
I couldnt find the current discussion regarding such query. So, giving my query in this thread. If possible, Please redirect it to correct thread. Thanks !!!
Thanks in advance.
Read about Commom Tabe Expression
You will get some idea on how to implement this
hi sir,
I learned lot about sql queries from your website. it’s simple and useful.
with regards,
A.Moorthy
Hi i would like to know the datatype of int
we have the option of number(4,2) in oracle
like this way we have anyother datatype is available in server
You can’t specify length for int datatype
You can however specify the same for decimal datatype
Hi all,
How can I convert a money value like 539393.00 (in milliseconds) to
time 00:08:59:393 (hh:mm:ss:ms)
in MS SQL
Kind Regards,
Luc
select dateadd(millisecond,539393,0)
thanks, it works fine.
but I only need the time, how can I remove the date?
I’m really greatfull and appreciate your help.
Where do you want to show data?
If you use front end application, do formation there
Otherwise
select left(right(convert(varchar(30),dateadd(millisecond,539393,0),109),11),9)
Hi all, my last problem
The next stored procedure works fine
use ExamenLoopClub
go
if exists
(select name from sysobjects
where name = ‘sp_WedstrijdAll’ and xtype = ‘p’)
drop procedure sp_WedstrijdAll
go
create procedure sp_WedstrijdAll
as
SELECT (w.WedstrijdID) as ID,
left ((convert(varchar(20),(w.Startijd), 113)),17) as Starttijd,
(w.Wedstrijdnaam)as Naam, ( i.LoperID)AS [#Deelnemers]
FROM tblWedstrijd w left join tblInschrijving i
ON w.WedstrijdID = i.WedstrijdID
The table tblWedstrijd contains more different WedstrijdID than tblInschrijving , so in that case the result is null.
Now I must count all the LoperID and show the result near Wedstrijdnaam. I change the SP to
use ExamenLoopClub
go
if exists
(select name from sysobjects
where name = ‘sp_WedstrijdAll’ and xtype = ‘p’)
drop procedure sp_WedstrijdAll
go
create procedure sp_WedstrijdAll
as
SELECT (w.WedstrijdID) as ID,
left ((convert(varchar(20),(w.Startijd), 113)),17) as Starttijd,
(w.Wedstrijdnaam)as Naam, count(distinct i.LoperID)AS [#Deelnemers]
FROM tblWedstrijd w left join tblInschrijving i
ON w.WedstrijdID = i.WedstrijdID
group by (w.Wedstrijdnaam)
I get : Column ‘tblWedstrijd.WedstrijdID’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
What’s the sollution for this?
This is my last SP, the the rest is CSharp (datagridview)
Thanks a lot for the help
I found it, Great site with study info, thanks a lot.
use ExamenLoopClub
go
if exists
(select name from sysobjects
where name = ‘sp_WedstrijdAll’ and xtype = ‘p’)
drop procedure sp_WedstrijdAll
go
create procedure sp_WedstrijdAll
as
select (w.WedstrijdID) as ID,
left ((convert(varchar(20),(w.Startijd), 113)),17) as Starttijd,
(w.Wedstrijdnaam)as Naam, y.[#Deelnemers]
from tblWedstrijd w cross join (
select distinct w.WedstrijdID , count(distinct loperid) as [#Deelnemers]
from tblInschrijving i right join tblWedstrijd w
on w.WedstrijdID = i.WedstrijdID
group by w.WedstrijdID
) as y
where w.WedstrijdID = y.WedstrijdID
order by w.Startijd desc
Hi!! This is a really nice blog. I hope you will be able to help me solve my problem.
I am creating a report using sql server 2005 and I need to convert numbers to characters like 345000 top three hundred and forty five thousand. Is this possible? If it is, please tell me how to go about it. Thank you
Crystal Reports has a function called toWords that can do this job. See if you are able to find a similar function in SSRS
I personally use CONVERT. One thing I like about your posts are that, you always compare similar methods of doing the same thing. This is very important to actually know the inner workings of those methods.
And the UDF to extract numeric from alpa-numeric is excellent !!!
Note that CAST is ANSI standard and CONVERT is not
You can use CONVERT for formatting the dates and money values
Hi
I have a table which is char datatype with data’s as 45689MB.63, 89586.21MB, 365MB like wise… I need to convert into just numericals without MB like 45689.63, 89586.21… i tried
select cast (column_name, float) from table_name
select cast (column_name, float) from table_name
but getting error as:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
please help, i need to find out DB sizes of around 75 server once in a week, now i’m doing manually. If i convert char to float it will reduce my 80% of the work.
Thanks in advance
Chandru. . .
Try this
select cast (left(column_name,patindex(‘%[a-zA-Z]%’,column_name)-1) as float) from table_name
I want to add Like operator in the below query ,
Select Username,Password from For_login where
Convert(varbinary,Password )=convert(varbinary, ‘t’)
so that ,i changed my query like this
Select Username,Password from For_login where
Convert(varbinary,Password )=convert(varbinary, ‘t’)like ‘t%’
it gives error
Please correct my query
Thanks
What happens when you try this?
Select Username,Password from For_login where
Password ) like ‘t%’
Hello,
I have a column with a data type as Money having values :
209.90
8.30
29.20
Now I want these values to be converted to Hours
For example
If you see 209.90 consider figure before decimal that is 209 and after decimal its 90
Now 90 means 1hr 30 Min.
So output result of 209.90 should be 210.30
How to achieve the below Result?
210.30
8.30
29.20
Apply this logic
select times,parsename(times,2)+parsename(times,1)/60 as hours,parsename(times,1)%60 minutes from
(
select 209.90 as times union all
select 8.30 union all
select 29.20
) as t
Hello,
I want to run a query to multiply two columns in SQL SERVER 2008. Two columns looks like as follows,
Column1 Column2
45652 1year
54865 12 Months
The issue is, Result shall be calculated month wise. i.e. 54865 * 12 = XYZ. Need to convert year value in months as well to get the desired results.
Tried using CAST but its not working. It gives following error
‘Error converting data type varchar to numeric’
I’ll be grateful if someone can help !
Thanks.
You need to normlaise the table.
select column1*case column2 like ‘%year%’ then left(colum2,charindex(‘[a-z]‘,column2)-1) else left(colum2,charindex(‘[ ]‘,column2)-1) end from your_table
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
Hi
i am new with this , i need help plz
i am trying to calculate max number and add 1 on it
the field called numb , and the max number on it is 00047 but its character( not numeric ) i want the result to be 00048 and replace it
select 1
CALCULATE MAX(NUMB) TO MYNUMB
SELECT 3
REPLACE NUMB WITH ‘MYNUMB+1′
any help plz
select MAX(NUMB*1) +1 from table
Hi , Its Gud
I am using Visual Studio 2008 to develop an ASP.NET VB application for a client. The database is MS ACCESS, which they provided. I created an AccessDataSource to make the connection to the DB and am using a FormView control on the page to display, edit, delete and insert records.
The FormView control provides various methods for displaying the data, most of which are of the TextBox variety, which is derived from ‘Templates’.
Viewing works fine, as does inserting new records. The problem I’m having is in Update and Delete. The reason for that, I am sure, is that the index (primary key) into the Access database table is an ‘Auto Number’ (Long Integer), which is displayed in the template inside a TextBox, which means it’s now a string and has to be converted (CAST) back to an Integer. This applies to both the UPDATE and DELETE strings.
Here is the DELETE string, as coded in the AccessDataSource template:
Result = the table
ID = the index into the table (primary key)
@IDLabel1 = the TextBox where the index (ID) is stored/displayed
DELETE FROM Results WHERE ID=CAST(@IDLabel1 AS INT)
The error returned in MSIE is;
Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression ‘[ID]=CAST(@IDLabel1 AS INT)’.
I haven’t gotten around to modifying the UPDATE statement yet and am assuming that once the DELETE statement problem is fixed, I can apply the same solution to the UPDATE statement.
Again, the contents of the TextBox (@IDLabel1) is the index into the table that identifies the record to be either DELETED or UPDATED. Any help would be greatly appreciated.
Thanks.
OldFart
Never mind. I figured it out. The error was due to NOT having populating the ‘Keys/DataKeyNames’ array in the asp:FormView section of the code.
By default, it (apparently) assumes that ALL fields are required to Update and/or Delete a record, which obviously isn’t the case. I added the primary key (“ID”) to the variable (i.e. DataKeyNames=”ID”), and Christmas came a couple of days early this year.
The whole thing is controlled by the ‘Keys’ dictionary. To quote MicroSloth;
“The Keys dictionary contains the names and values of fields that uniquely identify the record to update or delete, and always contains the original values of the key fields. To specify which fields are placed in the Keys dictionary, set the DataKeyNames property to a comma-separated list of field names that represent the primary key of your data.”
All this (and MUCH more) can be found in the MSDN article entitled; “Modifying Data Using a FormView Web Server Control”
Although everything is working just swell now, I think MS goes out of their way to make things as convolulted as they possibly can. I guess job security is REAL important to their developers. Go figure.
OldFart
Almost forgot. I didn’t have to use CAST, CONVERT or CInt or anthing else to get the Update and Delete commands to execute properly. Here’s why;
Apparently, the orginal values and their data types are all stored in the “Values dictionary collection”, which basically means that when those values are referenced/used, they are automatically converted back to their original data types and are used accordingly. Pretty clever. There are actually THREE dictionaries; the Keys dictionary, the NewValues dictionary, and the OldValues dictionary (what happened to the ‘MiddleAgedValues’ dictionary?!).
I’ve been developing software since 1975 (hence the ‘OldFart’ handle). Some of this new stuff is completely foreign to me and takes a while to sink in. This one took me 2 days to figure out. I’m learning … all over again.
OldFart
select cast(‘john’ as int)
–gives me error
–Conversion failed when converting the varchar value ‘John’ –to data type int
It is very clear. How can you convert characters to int?
hello sir ,i’m karthik from india i have one quesition in sql server
one field have “dep1″
how to split character and numeric .
how numeric values only increment like “dep2″
please reply the answer
thank you,
karthik.
select col,’dep’+cast(max(substring(col,4,1))*1+1 as varchar(10)) from table
what is the difference between cast and convert function? explain with simple example?
This post exactly describes what you needed
http://beyondrelational.com/blogs/madhivanan/archive/2010/07/22/cast-vs-convert.aspx
Hi,
How to convert in sql,
example number
1) 43 to be 0043
2) 3 to be 0003
Any suggestion?need help..
Thanks
Better to do it in front en application. Otherwise
select right(’0000′+cast(col as varchar(10)),4) from table
hi all,
i want to convert a numeric value into text. i’m using this code and this is successfully, but if i enter value such as 1234.56. the output will be One thousand two hundred thirty four.
the decimal value which is (.56) not appear. how i want to make the coding read the decimal value.
here is my coding
CREATE FUNCTION NumToWords
(@num numeric)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @cNum varchar(20)
SET @cNum = @num
DECLARE @len int
SET @len = LEN(@Num)
DECLARE @place int
SET @place = 0
DECLARE @digit varchar(1), @tens varchar(2)
DECLARE @res varchar(1000)
SET @res = ”
DECLARE @tblNum TABLE
(Num int, NumStr varchar(20))
INSERT INTO @tblNum
SELECT 1, ‘ ONE’ UNION SELECT 2, ‘ TWO’ UNION
SELECT 3, ‘ THREE’ UNION SELECT 4, ‘ FOUR’ UNION
SELECT 5, ‘ FIVE’ UNION SELECT 6, ‘ SIX’ UNION
SELECT 7, ‘ SEVEN’ UNION SELECT 8, ‘ EIGHT’ UNION
SELECT 9, ‘ NINE’ UNION SELECT 10, ‘ TEN’ UNION
SELECT 11, ‘ ELEVEN’ UNION SELECT 12, ‘ TWELVE’ UNION
SELECT 13, ‘ THIRTEEN’ UNION SELECT 14, ‘ FOURTEEN’ UNION
SELECT 15, ‘ FIFTEEN’ UNION SELECT 16, ‘ SIXTEEN’ UNION
SELECT 17, ‘ SEVENTEEN’ UNION SELECT 18, ‘ EIGHTEEN’ UNION
SELECT 19, ‘ NINETEEN’ UNION
SELECT 20, ‘ TWENTY’ UNION SELECT 30, ‘ THIRTY’ UNION
SELECT 40, ‘ FOURTY’ UNION SELECT 50, ‘ FIFTY’ UNION
SELECT 60, ‘ SIXTY’ UNION SELECT 70, ‘ SEVENTY’ UNION
SELECT 80, ‘ EIGHTY’ UNION SELECT 90, ‘ NINETY’
DECLARE @hundred varchar(200)
SET @hundred = ”
DECLARE @separatorUnit varchar(20)
DECLARE @nStr varchar(20)
WHILE @place 0
SET @res = @hundred + @separatorUnit + @res
SET @hundred = ”
END
SET @place = @place + 1
END
IF @hundred ” SET @res = @hundred + @separatorUnit + @res
RETURN @res
END
sorry,this is the full coding.
CREATE FUNCTION NumToWords
(@num numeric)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @cNum varchar(20)
SET @cNum = @num
DECLARE @len int
SET @len = LEN(@Num)
DECLARE @place int
SET @place = 0
DECLARE @digit varchar(1), @tens varchar(2)
DECLARE @res varchar(1000)
SET @res = ”
DECLARE @tblNum TABLE
(Num int, NumStr varchar(20))
INSERT INTO @tblNum
SELECT 1, ‘ ONE’ UNION SELECT 2, ‘ TWO’ UNION
SELECT 3, ‘ THREE’ UNION SELECT 4, ‘ FOUR’ UNION
SELECT 5, ‘ FIVE’ UNION SELECT 6, ‘ SIX’ UNION
SELECT 7, ‘ SEVEN’ UNION SELECT 8, ‘ EIGHT’ UNION
SELECT 9, ‘ NINE’ UNION SELECT 10, ‘ TEN’ UNION
SELECT 11, ‘ ELEVEN’ UNION SELECT 12, ‘ TWELVE’ UNION
SELECT 13, ‘ THIRTEEN’ UNION SELECT 14, ‘ FOURTEEN’ UNION
SELECT 15, ‘ FIFTEEN’ UNION SELECT 16, ‘ SIXTEEN’ UNION
SELECT 17, ‘ SEVENTEEN’ UNION SELECT 18, ‘ EIGHTEEN’ UNION
SELECT 19, ‘ NINETEEN’ UNION
SELECT 20, ‘ TWENTY’ UNION SELECT 30, ‘ THIRTY’ UNION
SELECT 40, ‘ FOURTY’ UNION SELECT 50, ‘ FIFTY’ UNION
SELECT 60, ‘ SIXTY’ UNION SELECT 70, ‘ SEVENTY’ UNION
SELECT 80, ‘ EIGHTY’ UNION SELECT 90, ‘ NINETY’
DECLARE @hundred varchar(200)
SET @hundred = ”
DECLARE @separatorUnit varchar(20)
DECLARE @nStr varchar(20)
WHILE @place 0
SET @res = @hundred + @separatorUnit + @res
SET @hundred = ”
END
SET @place = @place + 1
END
IF @hundred ” SET @res = @hundred + @separatorUnit + @res
RETURN @res
END
the following sql is not working.give a solution
Select isnull(max(convert(int,substring(BookCode,2,4))),’S')
from BookMaster
Hi,
i have a field of varchar data type, i want to use the “in” clause in the query to get the data,how it is possible?
e.g the data in the field is like ’50,60,70′ i want to get the data. the query is like
select * from tbl_test where cloumn_name in (’60′)
here tbl_test is the table name and column_name is the field name where ’50,60,70′ exist.
plz help me i searched alot but not get any solution..
thanks in advance.
Hi Dear
I have problem.I have two fields in my table as ID and Sub_ID.
ID column is for my items(1,2,3,….) and SUB_ID is for save sub items for each item Like (1-1,1-2,1-3 ,…..)
this is a sample 10 row of my table :
item sub_item
==================
1 1-1
1 1-2
1 1-3
1 1-4
1 1-5
1 1-6
1 1-7
1 1-8
1 1-9
1 1-10
1 2
1 3-1
1 3-2
……..
When I use this :
SELECT * FROM MyTable
ORDER BY ID, Sub_ID ASC
items 1-10 comes before 1-1 on sort like
1 1-10
1 1-1
1 1-2
……
How can I solve this problem?
I used your comments in this page nd i wrote this:
select id , substring(Sub_ID,1,(charindex(‘-’,Sub_ID)-1)) as new_sub_id
but it could’nt work.Can u help me Please?
Kind Regards
BAbak
HI,
Were you able to find a solution for the above problem?
Hi all,
i hope you can help me
in a table EventLog, i have a column nDaeTime(INT) in secondes( from 1970/01/01).
I want to convert and write all the value of nDateTime(INT) to a column in a separate table named LogRecord in the column DateTime(varchar(50) with the following format: DD/MM/YYYYHH:MM:SS.
i.e:
tbl_EventLog tbl_LogRecord
nDateTime DateTime
1271860947 ———-> DD/MM/YYYYHH:MM:SS
How can i do it ?
many thanks,
laurent
Tengo un problema, tengo la tabla empleado(codigo,nombre) el caso es que “codigo” tiene valores ‘XP0001′,’XP0002′,’XP0003′… lo que quiero es insertar nuevo empleado desde una pagina web pero como entrada solo tiene “nombre”; y “codigo” debe autogenerarse, osea al insertar nuevo NOMBRE de empleado, internamente el SQL debe insertar el nuevo CODIGO de empleado, como lo hago???
gracias.
how can i select number of thousands in a sum of amount
in sql 2000
ex: 56780 means i need the result as 56 thousands
I have the data like ’200911′ ,’201124′,’AVV,CVR,BXY’
in which ’200911′ is the start date( i.e 2009 is the year and 11 is the work week
and the second value is ’201124′ is the end date (i.e 2011 is the year and 24 is the work week). and the next value is the first 3 letters of the record name… which is a multiple list of record names
I need to pull the records from a table called sn_master that consists of a ship date in it which is in standard date format…
I need to pull the records from the starting day of the given first parameter value of work week to the ending day of the second parameter value of the work week following the 3 characters of the record name in the where condition…
for this above conditions… I have written this query which is working fine in 4 seconds… but I’hv been told that to write directly with out using parameter @sqlquery… as user dont have exec permissions, so I was trying to run directly removing it but its taking long time of 4 to 5minutes, can anyone help me whts the problem was….
1.
SET @sqlQuery = ‘INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(”0” + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >=”’+ @startdate + ”’AND
shpm.shipdate =”+ @startdate + ”AND
shpm.shipdate <='' + @enddate + '' AND
snm.sn like ''+ @sn + '%' order by shpm.shipdate desc
Kindly help me out where its getting wrong…..?
and here is the query without @sqlquery parameter
INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(’0′ + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >=”+ @startdate + ”AND
shpm.shipdate <='' + @enddate + '' AND
snm.sn like ''+ @sn + '%' order by shpm.shipdate desc
Kindly help me out where its getting wrong…..?
How do you maintain the value of a number that is being received as an int? For example, I have the value ’0123′ defined as a varchar(4) in one table and value ’123′ being received in another table. I need that leading zero. How do I convert the data type, but not lose the leading zero?
If the datatype is integer it is not possible
hello,
help please,
i have a datetime variable named ‘date’ , i want to save it to sql database.
when i save try to save to database it is no longer 10/06/2011 (june, ten), it is become 06/10/2011 (october, six)
what an i doing wrong? i am using c#
this is how i get the date:
string date = DateTime.Now.ToString(“dd/MM/yyyy”);
also this is my first time trying to create a program, please explaine as much as you can.
thanks a lot, Boosh.
You should always use unambigious date formats. Refer this to know more about this
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
Hi Dave, I looked into this blog a lot, my regards for a very good job.
I checked the blog to see if may question had been answer before but no luck
:(
this is it:
I need to search for values in a column which is composed of Julian dates,
the values have this mask: [Y][ddd][XXXXXX] so the actual value would be: 8092800033 which corresponds to 04/01/2008 that
I need to select values in a range for instance
where 1=1
and (x like ’8092%’ or x like ’8093%’ or x like ’8093%’)
corresponding a 3 different days, from 04/01/2008 to 04/03/2008
my question to you is, how can I use the like operator or any other operator to avoid placing every number but rather a range, since something as:
where 1=1
and x like ‘[8092 -8093]%’
didn’t work for me?
do you have any insights on this?
hi im trying to convert to integer so when i put this in it doesnt round down
DATEDIFF(mi, dbo.JobTracking.JobCallTime, dbo.BillyCalendarTable.[Time Finished]) / 60
hi,
i have a table like this:-
cardId issusedate
1 2009-01-18 10:15:42.000
2 2009-01-18 10:15:49.000
3 2009-02-11 08:43:51.000
4 2009-03-18 11:15:44.000
5 2009-03-18 11:15:52.000
6 2009-03-18 11:16:11.000
7 2009-04-18 11:16:19.000
8 2009-04-11 09:15:02.000
9 2009-05-18 01:10:23.000
10 2009-05-18 01:10:42.000
11 2009-06-18 01:10:50.000
12 2009-06-18 01:11:09.000
13 2009-06-18 01:11:17.000
14 2009-06-18 01:11:36.000
i want output like this:-
jan feb mar april may june
2 1 3 2 2 4
number of card issues, monthwise…
thanks
Abhishek
Look at this PIVOT post
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
hi. i need your help in sorting numbers.
i have these numbers in numbering field :
3.1,3.2,3.3,3.4,……,3.11, 3.12
and i want it to be displayed as these:
3.1
3.2
3.3
..
..
3.11
3.12
the problem is, it doesn’t appear that way.
so i used CAST.
e.g:
CAST ([numbering] AS DECIMAL (4,2)) as NUM
–ORDER BY NUM ASC;
and it becomes:
3.10
3.10
3.11
3.20
3.30
how can i make it to be displayed this way? =>
3.1
3.2
3.3
..
..
3.11
3.12
pls help. thanks.
This is more of the formation issue that should be done in the front end application.
You can replace . with ,
then convert
I need help!! I am trying to convert the following data that I am pulling from a database:
Date Time Duration
20110718 30000 405
And I want it to look like:
Date Time Duration
07/18/11 3:00:00 AM 00:04:05
I keep encountering the following error message: “Conversion from type ‘Integer’ to type ‘Date’ is not valid.”
Is there a way to convert this data?
What are the datatype of these columns?
All three are (int, not null).
Try this
declare @t table(Date int, Time int , Duration int)
insert into @t
select 20110718 ,30000 ,405
select cast(cast(date as CHAR(8)) as datetime) as date,stuff(stuff(right(’000000′+cast(time as varchar(100)),6),5,0,’:'),3,0,’:') as time,
stuff(stuff(right(’000000′+cast(Duration as varchar(100)),6),5,0,’:'),3,0,’:') as duration from @t
Hi, Please check this solution
http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/
and
http://blog.sqlauthority.com/2009/08/06/sql-server-get-time-in-hourminute-format-from-a-datetime-get-date-part-only-from-datetime/
Hi All,
I need some help. I’m trying to caluculate some overtime. Our system logs the start time and end time of each taks. I need to know who finishes after 17:00 and how many minutes/hours they have done after 17:00.
So far I managed to extrat the time, but it is return in Char and cannot make any calculation.
Thanks for your help.
SELECT dbo.Staff.full_name AS Name, dbo.Tasks.title AS Taks, dbo.Project.title AS Project, dbo.Worktrans.tran_date AS Date,
dbo.Worktrans.work_description AS Description, CONVERT(varchar(10), dbo.Worktrans.start_time, 108) AS StartTime, CONVERT(varchar(10),
dbo.Worktrans.end_time, 108) AS EndTime
Hello,
I would like assistance in assigning a certification number using the CAST SQL command.
Below is the snippet of the code that assigns the certification number:
UPDATE Cert SET TrackingNo = CTypeID + ‘-’ + CAST(@ApplicantNo AS VARCHAR)
While the above works for my organization, I would like to know how to assign certification numbers using a 5-number format. The code above assigns certification numbers starting with 1 and up, i.e. R-1 to R-5497. I am looking to have R-11111 assigned and so on with all the numbers being 5 digits.
Thank you for your assistance.
Scott
hello,
i have to use between query to select date with format(100). but i cant convert it in 100 format. plz help me
i have to convert date through UI
Thanks for this valuble information
Hi Sir,
I want to convert money into words throw SQL PROCEDURE Like:-29872.50 in Words Twenty Thous. Eight Hund. and Fifty Paise Only
Can Any one Help me
Thank You
i want to convert numeric values in word using function in oracle,plz help
eg:20134
twenty thousand one hundred thirty four
This site is for MS SQL Server. For Oracle queries post your questions at oracle related sites
hi please help
i am getting the following error im new on SQL
–Msg 8115, Level 16, State 2, Line 11–
–Arithmetic overflow error converting expression to data type datetime.–
select a.*,WAGETYPE,DRREPAYMENTMETHOD,LASTSTRIKEDATE,SALARYDAY,lastreceiptdate into #1
from mis..ptpreport_detail a left join EDWDW..tbdebitorder b (nolock)
on a.loanrefno=b.LOANREFERENCE
where DRREPAYMENTMETHOD=’FLD’ and a.Status=’Pending’ and RepayMethod=’EFT’
and TemplateName in (‘CCConsultant’,
‘NewCallCentreWorkFlow’,
‘Call Centre Agent Super User’)
select distinct a.loanref,a.[Narration 1],b.WAGETYPE,cast(CONVERT(varchar(10),b.LASTSTRIKEDATE,112) AS datetime) LASTSTRIKEDATE,b.ptpduedate,
PTPMAdeDate,failedreasoncode1, b.lastreceiptdate, c.lastreceiptdate
from EDWDW..vw_AbilNetreceiptsAllUnion a left join EDWDW.#1 b (nolock)
on a.loanref=b.loanrefno
left join PhaseII..ACCOUNTDETAILS (nolock)
on a.loanref=PhaseII..ACCOUNTDETAILS.loanrefno
left join EDWDW..tbdaily c(nolock)
on a.loanref=LoanRefId
where a.valuedate between convert(varchar(8),b.PTPMadeDate,112) and
convert(varchar(8),b.PTPDueDate,112)
and a.Amount>0 and B.ptpduedate>getdate()
and c.lastreceiptdate > ’2011-09-01 00:00:00.000′
and failedreasoncode1 is null
and b.LASTSTRIKEDATE =b.PTPMAdeDate
and [narration 1] not in (‘Payment Stopped’,'Debits Not Allowed’,
‘Debit In Contravention’,'Authorisation Cancelled’,'Debits Not Allowed’,
‘Account Frozen’,'No Authority To Debit’,'Previously Stopped’,'No Such Account’,
‘Account Closed’)
What is the datatype of the column LASTSTRIKEDATE?
how to change number to words.
for example, 100 to hundred.
I have this exemple(SQL Server 2008):
SET XACT_ABORT OFF
BEGIN TRY
BEGIN TRANSACTION
PRINT convert(int,’abc’)
–insert …
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT ‘Convert fails’
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION
print ‘ROLLBACK’ — <————————-
END
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION
PRINT 'COMMIT'
END
END CATCH
When the convert fails inside a BEGIN TRY…CATCH the transaction cannot be committed (always end on print 'ROLLBACK' ). Is there any way to solve this?
Como faço p converter esse inteiro em string; SEELCT * FROM TABELA WERE indice = Convert(int,”digite o indice:”);
INSERT INTO Table1([Language],[Value],[ShortDescription],[LongDescription],[DisplayOrder])VALUES(‘English’,'None’,null,’one’,1)
Value column is primarykey and not allow null
When I am trying to insert its shows “Error converting data type varchar to float.” If i use any string value it’s not working(‘None) in the value column .If I use numeric value it’s inserting(’123′).How to resolve this
Im trying to set a feild that is a string from a integer feild how can i do this:
set refno=batchno
where refno is actually a string(text feild)
Hi, this is an amazing blog. Well … I’m with a little problem with the conversion of a text string to number. Mainly the problem is that the string can come dirty. For example ’123 /. where always the “dirt” is abut the beginning or end of the string, but it is not its length.
The errors I get is this:
Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value”123 ‘to a column of data type int.
Without traversing the string, letter by letter and without cursors, there is a solution “elegant” and simple for this problem?
Thank you very much!
sanjay
Your comment is awaiting moderation.
HI,
CAN ANYBODY HELP FOR BELOW ERROR.
THIS IS MY CODE
DECLARE @i INT
DECLARE @COUNT VARCHAR(8)
DECLARE @RowCount INT
DECLARE @Query Varchar(100)=”
SELECT @Query = ‘SELECT COUNT(*) FROM @Emp’
SET @i=1
DECLARE @Emp Table(IdEmp INT Primary Key Identity(1,1),Id INT )
INSERT INTO @Emp SELECT DISTINCT AddressId FROM Tb_Employee
Select * from @Emp
SET @RowCount= CAST ((@Query) As INT)
Here I Am Coverting @Query To INT. But I geeting Error
As Conversion failed when converting the varchar value ‘SELECT COUNT(*) FROM @Emp’ to data type int.
Instead of
SET @RowCount= CAST ((@Query) As INT)
use
SET @RowCount= (SELECT COUNT(*) FROM @Emp)
Hi Madhivanan
Thank U For The Reply It Worked For ME
HELLO , I WANT TO SEPARATE A SIX DIGIT NUMBER INTO INDIVIDUAL DIGIT IN SQL SERVER…..FOR EXAMPLE…123456 AS 1 2 3 4 5 AND 6.
This is one of the methods
declare @n int
set @n=123456
select stuff(stuff(stuff(stuff(stuff(@n,2,0,’ ‘),4,0,’ ‘),6,0,’ ‘),8,0,’ ‘),10,0,’ ‘)
Which SQL function ll use to convert 2567 in the form of “two thousand five hundred sixty seven” ?
Hi Need a Solution Like
Student_Table:
ID(bigint) Name(varchar) SubjectID(varchar)
1 x1 1,2
2 x2 2,3
3 x3 2,3
Subject_table
ID(bigint) Name(varchar)
1 S1
2 S2
3 S3
I Need a result LIke
ID Name SubjecyName
1 x1 S1,S2
2 x2 S2,S3
3 x3 S2,S3
How Can i get the above result….?
Regards
Naag
hi,
i want to conver string as ’2011 11′ into a number like 201111.
how can i do that?
10′x!
Anna
Hi,
Try the below code:
DECLARE @yrmth VARCHAR(15)=’2011 11′
SET @yrmth=REPLACE(@yrmth,’ ‘,’.')
SELECT CAST(PARSENAME(@yrmth ,2)+PARSENAME(@yrmth,1) AS INT)
select replace(’2011 11′,’ ‘,”)*1
I want to cast a varchar and only take the numeric characters and make an int.
Is this posible?
Sorry the bad writing, im spanish.
You [two words removed]
Explicit conversion from data type text to int is not allowed.
hello I need some problem on following code:
SELECT REP,A.DEALER,A.PROD AS PRODUCT,OPENING,PRI,SEC,PRI-SEC+OPENING AS CLOSING,PRI_AMT ,SEC_AMT FROM (
SELECT VNO,DATE_T AS DATESAL,ACCOUNT AS DEALER,PRODUCT AS PROD,SUM (convert(int,QUANTITY))
AS PRI,NET_AMOUNT AS PRI_AMT FROM SALES_DETAILS WHERE QUANTITY’ ‘ GROUP BY ACCOUNT,PRODUCT,DATE_T,NET_AMOUNT,VNO) A, (
SELECT REPRESENTATIVE_NAME AS REP,DATE_SALES AS DATESAL,DEALER_NAME
AS DEALER,PRODUCT_NAME AS PROD,SUM(convert(int,OPENING_STOCK))AS
OPENING,SUM(convert(int,SECONDARY_SALES))AS SEC,SUM(convert(int,SECONDARY_SALES) )*RATE
AS SEC_AMT FROM SALES_ENTRY_DETAILS GROUP BY
PRODUCT_NAME,DATE_SALES,DEALER_NAME,REPRESENTATIVE_NAME,RATE) B
WHERE A.DATESAL=B.DATESAL AND A.DEALER=B.DEALER
AND A.PROD=B.PROD AND CONVERT(date,B.DATESAL ,103) BETWEEN CONVERT(date,’10-10-2010′,103)
AND CONVERT(date,’10-10-2012′,103)
In the above coding PRI – SEC + OPENING occurs error
Hello I need some help
I’m trying to retreive the Min and Max value of a field that contain the following, fractions numbers and Text.
Name Finding Name Value
Jason First Test Pass
Jason Book BP 113/67
jason Book BP 115/80
Jason Book BP 160/80
Jason Book Temp 100
Jason Book Temp 99
Jason Book Temp 95
When I use the following code MAX(value)
I receive:
Jason First Test Pass
Jason Book BP 113/67
Jason Book Temp 99 this number should be 100 since 100 is the MAX value in Book Temp filed.
Can someone help me
Hi I am using SQL server 2008, I have a problem in retrieving numeric values from the below data.
problem
———–
1
1 – Critical
2
2 – High
3
3 – Medium
4
4 – Low
5
———-
From the above values of Problem field, I want to retrieve only numeric values and numeric part of the values. please help me with the suitable query ASAP.
Thanks in advance
select substring(problem,1,patindex(‘%[^0-9]%’)-1) from table
Hi,
I have a varchar value like this ’00001342470′
How can I get rid of all the zeros (only at the begining) to show it like this
’1342470′?
Thank you in advance
select cast(col as int) as col from table
hi
i need a style format value for MM/dd/yyyy hh:mm:ss AM
For eg like this 02/05/2012 12:20:23 AM
Nithin
Either use a convert function or do it in front end application
ALTER PROCEDURE Proc_Filter_Mobile
– Add the parameters for the stored procedure here
@MOBSER_ID SMALLINT
AS
BEGIN
DECLARE @INT_POS SMALLINT,
@ANI NVARCHAR(20)=NULL,
@CUR_ANI NVARCHAR(1)=NULL,
@FLAG TINYINT,
@ANI_ID INT,
@ANI_SUB NVARCHAR(6)=NULL,
@COUNT TINYINT=0;
DECLARE @CUR_FILTER CURSOR;
SET NOCOUNT ON;
DECLARE @CHK_ANI TINYINT;
CREATE TABLE #Bulk_Load_Mobile_Series
(
Load_ID BIGINT IDENTITY(1,1),
MobileSeries_SeriesID int,
MobileSeries_ANI nvarchar(20)
)
– ## SETTING CURSOR ##
SET @CUR_FILTER=CURSOR FOR
SELECT DataTemp_ID,DataTemp_ANI FROM Bulk_DataTemp;
– ## OPENING CURSOR
OPEN @CUR_FILTER
–## FETCHING CURSOR VALUE
FETCH @CUR_FILTER INTO @ANI_ID,@ANI
WHILE @@FETCH_STATUS=0
BEGIN
–## CUTTING LAST FIVE CHARACTER FROM ANI
SET @ANI_SUB=SUBSTRING(@ANI,9,5);
–## CHECKING GOLDEN,SILVER AND PLATINUM NUMBER AND FILTERED
SET @INT_POS=0;
WHILE @INT_POS<=9
BEGIN
SET @CHK_ANI=1;
SET @FLAG=0;
WHILE @CHK_ANI=3)
BEGIN
SET @COUNT=1;
END;
–PRINT ‘CURRENT ANI -’ + @CUR_ANI;
–PRINT CAST(@INT_POS AS VARCHAR);
– PRINT ‘FLAG VALUE’ + CAST(@FLAG AS VARCHAR);
END;
ELSE
BEGIN
SET @FLAG=0;
END
SET @CHK_ANI=@CHK_ANI+1;
END
SET @INT_POS=@INT_POS+1;
END
IF(@COUNT1)
BEGIN
–### DELETE THAT RECROD FROM THE TABLE###
– PRINT @ANI_ID;
INSERT INTO #Bulk_Load_Mobile_Series(MobileSeries_SeriesID,MobileSeries_ANI)
VALUES(@MOBSER_ID,@ANI);
–DELETE FROM [DBO].Bulk_DataTemp
–WHERE DataTemp_ID=@ANI_ID;
SET @FLAG=0;
SET @COUNT=0;
END
ELSE
BEGIN
SET @COUNT=0;
SET @FLAG=0;
END;
FETCH @CUR_FILTER INTO @ANI_ID,@ANI
END
CLOSE @CUR_FILTER;
DEALLOCATE @CUR_FILTER;
SELECT * FROM #Bulk_Load_Mobile_Series
END
GO
I have a doubt — I am moving data from SQL to ORACLE DW and The column in SQL has char(23) and I am using substring of this CTRLNUM to get SOME ID and moving into Data-Warehouse.
How should I convert this char into Number to load into Data warehouse.
I am using something like this –
Convert (int,SUBSTRING(CTRLNUM,3,10)) AS LIMID..
It says — Conversion failed – varchar to data-type-int
It means that it has some bad data that cannot be converted to INT. First do a select statement and see what it returns
Hi all,
I am using Sybase 12.5 and not SQL and not sure if anyone can help.
Anyway the question is:
I have a table with a column that has customer names and branch names. Branch names start with number (less than 1000) and need to know if I can select only the branches from the table. The problem is Sybase 12.5 does not have functions. Functions came into existence only in v15.x
My statement is:
SELECT * FROM Customers WHERE CONVERT(INT, LEFT(Name,3)) < 1000
This throws up an error when it encounters the first record that's not starting with number.
Any help would be much appreciated.
Nissar
Hi All
I have a table with a column Zip cod (varchar), and I want to filter data based on condition (which would be numeric)
Something like this:
Select * From Address WHERE (CONVERT(INTEGER, Address.ZipCode) >= 1000 AND CONVERT(INTEGER, Address.ZipCode) <= 5000)
AND ISNUMERIC(Address.ZipCode) = 1
but the same is throwing error as we have values like '-', '50-45252' in the DB column.
Error: Conversion failed when converting the varchar value '.' to data type int
Pl. help me to solve this.
Hi All
I need a small help
———–
I have a nvarchar value something like ’1,3,5,6′, in my database table
But I want to use this in a where condition, something like this where event_id IN (1,3,5,6)
How do I achieve that, How can I convert it and use in the IN of a query
————-
Are you passing values via parameter?
I have the following script. Everthing works EXCEPT the last 2 lines regarding Voter.PARITY. I think I need to convert A.ADDR_HN to an integer again but it isn’t working when I enter the CAST function as similarly done in the previous 2 lines regarding Voter.MIN/MAX_RNG_3. Can anyone help?
– ===========================================
– Set Flag on addresses that are in address range…
– ==========================================
UPDATE gis_prod.SDE.TMP_ADDRPT_FOR_VOTER_LOCATORS –AddressPoint
SET TMP_IN_RNG_FLG = 1,
TMP_IN_RNG_OID = Voter.OBJECTID,
– TMP_ADCO_VALFLG_CL = Voter.VAL_FLG,
TMP_PRECINCT_CL = Voter.PRECINCT
FROM
gis_prod.SDE.TMP_ADDRPT_FOR_VOTER_LOCATORS A –AddressPoint A
INNER JOIN gis_prod.sde.TMP_ADCO_VoterLocator_City Voter
ON
A.ADDR_ALPHA = Voter.ADDR_ALPHA AND
(
cast (A.ADDR_HN as int) >= Voter.MIN_RNG_3 and
cast (A.ADDR_HN as int) <= Voter.MAX_RNG_3
)
AND
(
(A.ADDR_HN % 2 = 0 AND Voter.PARITY = 'EVEN') or
(A.ADDR_HN % 2 = 1 AND Voter.PARITY = 'ODD')
)
GO
I have the data like 1333200.3900 in a nvarchar column. I need to convert it into numeric…
Hi guys,
I am creating web form insert record to sqlserver, But i get a trouble like this: error: converting data type varchar to float. For handle it I try to using cast and convert. But until right now i still get trouble Incorrect syntax near ‘textbox1′. Any one can help me? Thanks.
=========================================
Dim typlat As Double = txtLat.Text
Dim typlot As Double = txtLon.Text
Dim adl As String
adl = CSng(Val(typlat))
adl = CSng(Val(typlot))
strSQL = “SELECT CAST(Latitude as float) from DBREPORTCUSTOM”
strSQL = “SELECT CONVERT(Double ‘” & typlat & “‘ ‘” & typlot & “‘) from DBREPORTCUSTOM”
===============================================
Hi,
I have a nvarchar value = ” Fee Component =Agency FeeÆFee=1600.00000000000000000ÆRemarks=Æ “, i want to convert the 1600.000000000 value to 1600.00 in this nvarchar value. So please suggest me the query for this.
Thanks,
Anki
Thank Bro.
You are a grate man. it helps me lot.
This is my select quary in sql 2005
SELECT EPFNo, yr, monthName, shftDate, shiftDesc, DateTim
FROM dbo.DayOffView
WHERE (DateTim >= CONVERT(DATETIME, ’2012-03-15 00:00:00′, 102)) AND (DateTim <= CONVERT(DATETIME, '2012-4-17 00:00:00', 102)) AND
(EPFNo = '2374')
Use these approach for accuracy and error free
http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-iii.aspx
I have a column name WBS varchar(100) – values willl be like 27, 27.1,27.2 ,27.2.1 etc…
now i need to get max wbs from that table, but the problem is, it is giving properly upto 0-9 it crosess like if 27.9, 27.10 then it is giving only 27.9 as max WBS? but actually max wbs is 27.10
please give me some suggestions. its very important to me.
this is my Query:
(select max(wbs) from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396)
i tried in following ways:
(select cast(isnull(max(WBS),0)as float) as wbs from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396)
(select Convert(float,(isnull(max(WBS),0)) as wbs from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396)
even itried for decimal also. didnt find any result.
First advise is to store these values in decimal datatype column. Try this
select max(WBS*1.0) as wbs from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396
Ya actually, intially our requirement was not like that thts y at tht time it is varchar recently it changed thts y, i tried to convert datatype to decimal bt its giving error tht error converting varchar to datatype decimal…
i tried the thing suggested by you,its not working..
canu please suggest another.
Thanks
What did you mean by “it is not working?” Did you get error?
I mean , same result is getting after trying urs also…
upto 0-9 only it taking 9 as max and giving result as 10…
You have multiple decimal points that can not be considered as numerals
SELECT TO_CHAR(TO_DATE(’10/10/10′,’YY/MM/DD’),’DD’)+MONTHS_BETWEEN(’10-OCT-10′,’10-AUG-10′)FROM DUAL;
What will happen when above statement is executed???
A.102 gets printed
B. Query fails as characters cannot be added to numbers
C. 12 gets printed
D. MONTHS_BETWEEN function fails
Plzzz explain
You have posted the code thats works in ORACLE. But this site focuses only on SQL Server
i came to know ans is 12 but how ???
select data.*
/*
,CASE WHEN data.counselor_name IS NULL
THEN c.combined_cem_quota
ELSE ”
END combined_cem_quota
*/
,CASE WHEN data.counselor_name IS NULL
THEN convert(DECIMAL(10,2),c.paf_quota)
ELSE ”
END ‘paf_quota’
,CASE WHEN data.counselor_name IS NULL
THEN convert(DECIMAL(10,2),c.heritage_quota) –c.heritage_quota
ELSE ”
END ‘heritage_quota’
FROM
(
select l.sup_emp_code,l.sup_name,raw.counselor_name,raw.sales_contract_nbr
,(SUM(raw.an_oc) ) l_an_oc
,(SUM(raw.heritage)) l_heritage
,(SUM(raw.merchendise)) l_merchendise
,(SUM(raw.paf)) l_paf
from
( select distinct sup_emp_code,sup_name from counselor_hierarchy where sup_emp_code 0 ) l
JOIN counselor_locations c on c.emp_no = l.sup_emp_code and c.override = 1
JOIN gor_data_raw raw on charindex(c.location_volume,raw.location_cd) > 0
GROUP BY l.sup_emp_code, l.sup_name,raw.counselor_name,raw.sales_contract_nbr with rollup
) data
JOIN counselor_locations c on c.emp_no = data.sup_emp_code
–WHERE data.sup_emp_code IS NOT NULL
Q. why is this program doing conversion?
I have put convert function but it still giving error?
define me convert function in sql
I need to convert a table of 1′s and 0′s to yes and no in SQL Server 2008.
I have no idea how to write the query. I am using a software that grabs table info from the server to update reports.
You can use case.
Case when fieldname = 1 then ‘yes’
when fieldname = 2 then ‘no’
else ‘-’ end
Our company is converting over from an old HP3000 environment to an HP-UX environment. An ad-hoc reporting tool called DataExpress did calculations using actual characters such as “{” . We are attempting to convert those characters into SQL queries and of course we get an error: “Conversion failed when converting the nvarchar value ‘{‘ to data type int.” Has anyone ran into this before or perhaps can direct us where to look for a solution to this issue?
Hello sir,
We have a file in AS400 iseries that defined a numeric field as character filed,
when I view it in SQL, it showed up as 885Q, but it is actually 8858-, how do
I change this field to show up as “8858-” in SQL? Please help. Thanks
Fannie
Hi Dev how do we convert 10.2.123 to 10.2
How do I convert this
(select ‘Total Taxable:’+convert(varchar(12),sum (tax_val),)
from [pa-svr-man01].r_flosceola.dbo.vw_OpenAccts)
So that my answer will come back as 1,000,000,000
Thanks
I needed to be more specific my answer is coming back as 15098738203 and I want it to convert to 15,098,738,203
Thanks
Getting an error as “Error converting data type varchar to numeric.” for below statement. Can anyone suggest a way forward..?
select CONVERT(numeric,’24,00′)
I m looking for something that I can convert–
’000-2.52′ should be -2.52
’00002.52′ should be 2.52
I can do the convert(decimal(11,2), ’00002.52′ ) but for negative I cannot do, please suggest, but the data that is coming in the file, so really can’t say when the data is postive or negative.
Please help.
I have put convert function its working Thx a lot
String Value Convert to INT, this query working thx a lot