Journey to SQL Authority with Pinal Dave

Posted in Best Practices, Pinal Dave, SQL, SQL Authority, SQL Function, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 87 Comments

87 Responses

1. on August 8, 2007 at 2:34 pm | Reply Swamy

   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

   
   
2. on August 9, 2007 at 12:56 am | Reply Michael

   Hi Swamy,

   Try:

   CONVERT(CHAR, @m,103)

   The 103 indicates date format dd/mm/yy. 101 would give you American.

   Michael

   
   
3. on September 19, 2007 at 3:51 am | Reply aswini

   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

   
   
4. on September 19, 2007 at 10:14 pm | Reply jvchoudary

   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

   
   
5. on October 2, 2007 at 6:34 pm | Reply pinaldave

   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 )

   
   
6. on October 3, 2007 at 11:13 am | Reply Judie

   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.

   
   
7. on November 27, 2007 at 10:03 pm | Reply Mithun

   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.

   
   
8. on December 22, 2007 at 2:48 am | Reply Anand

   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.

   
   
9. on December 27, 2007 at 7:35 am | Reply CoolDeep

   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.”

   
   
10. on January 4, 2008 at 12:23 pm | Reply natarajan

    how i convert varchar (6/7) this type into int

    
    
11. on January 10, 2008 at 8:20 pm | Reply patel

    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?

    
    
12. on January 16, 2008 at 9:33 am | Reply Rao

    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

    
    
13. on February 28, 2008 at 2:30 pm | Reply LV

    hi, how do convert the follwing data into single digit? without any decimal places and zeros? thanx…

    from “001.000″ to “1″ ?

    
    
14. on March 10, 2008 at 5:34 pm | Reply Deendayal saini pilani

    hi;
    natarajan u should be used as convertion of floting point expression

    select convert(int,(select 8/7))

    
    
15. on March 16, 2008 at 1:36 am | Reply gram1138

    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

    
    
16. on April 2, 2008 at 4:06 am | Reply Charles

    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.

    
    
17. on April 4, 2008 at 1:51 am | Reply vipul

    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

    
    
18. on April 9, 2008 at 12:08 pm | Reply Sam

    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

    
    
19. on April 16, 2008 at 1:24 am | Reply rk

    Vipul,

    You can use replace function

    replace(‘398-768-6789′,’398′,’(398)’)

    
    
20. on May 9, 2008 at 2:25 pm | Reply innovatel

    Thank you very much … I did not remember the syntax .. you help me :)

    Thanks and bye :)

    
    
21. on May 19, 2008 at 12:03 pm | Reply preethi

    is there is any sql query that,if i give “1″ as input,the output should be “one”.help me to solve this query

    
    
22. on June 11, 2008 at 8:51 pm | Reply Sharon Ba

    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

    
    
23. on June 18, 2008 at 1:37 am | Reply Sharath

    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

    
    
24. on June 19, 2008 at 9:53 am | Reply rania

    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

    
    
25. on July 22, 2008 at 9:06 pm | Reply rice

    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!

    
    
26. on July 23, 2008 at 10:44 am | Reply congenial

    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.

    
    
27. on July 24, 2008 at 11:34 am | Reply mohan

    how to allow ‘ (single cotes) in a text box for inserting into an sql db

    
    
28. on July 28, 2008 at 2:29 pm | Reply Farid Masood

    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

    
    
29. on August 4, 2008 at 2:54 pm | Reply dharmendra rawat

    hi

    i have a feild phone as varchar like 98+e00012
    i want convert that data into numeric

    
    
30. on August 6, 2008 at 12:02 am | Reply Satya

    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!

    
    
31. on August 22, 2008 at 11:26 am | Reply Sach

    HI

    I want to transfer data from excel file to sqlsever 2005 table

    What should I use???

    
    
32. on August 29, 2008 at 7:10 pm | Reply Gabriela

    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

    
    
33. on August 30, 2008 at 11:18 am | Reply Imran Mohammed

    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.

    
    
34. on September 8, 2008 at 4:12 pm | Reply Amina

    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

    
    
35. on September 18, 2008 at 11:40 am | Reply A. JOSHUA

    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

    
    
36. on September 22, 2008 at 8:50 pm | Reply Carlos Barini

    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.

    
    
37. on September 23, 2008 at 6:16 am | Reply Imran Mohammed

    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.

    
    
38. on October 1, 2008 at 12:44 pm | Reply poonkodi

    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

    
    
39. on October 4, 2008 at 10:50 am | Reply rakesh

    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.

    
    
40. on October 7, 2008 at 11:26 pm | Reply BG

    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?

    
    
41. on October 21, 2008 at 11:14 pm | Reply girlseafarer

    How do I convert a string of numbers into integer?

    
    
42. on October 22, 2008 at 8:19 am | Reply Imran Mohammed

    You cannot convert character to Integer. Not using CAST and Convert function.

    Thanks,
    Imran.

    
    
43. on November 14, 2008 at 3:06 am | Reply cbr600gyrl

    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!

    
    
44. on December 2, 2008 at 2:04 pm | Reply smita

    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.

    
    
45. on December 5, 2008 at 8:20 am | Reply jothi

    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

    
    
46. on December 5, 2008 at 9:52 am | Reply Imran Mohammed

    @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.

    
    
47. on December 13, 2008 at 4:26 pm | Reply swati

    Please Tell me from wherei can download sql sever 2005

    
    
48. on December 14, 2008 at 3:12 am | Reply Imran Mohammed

    @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

    
    
49. on December 16, 2008 at 2:22 pm | Reply Jenny

    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

    
    
50. on December 25, 2008 at 2:58 pm | Reply Asma qureshi

    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)

    
    
51. on January 2, 2009 at 5:57 pm | Reply saleel

    How can convert integer to time(hh:mm:ss) in sql server 2005. I am getting time as in seconds.
    Please help me.

    
    
52. on January 2, 2009 at 6:25 pm | Reply saleel

    I got solution
    SELECT CONVERT(varchar, DATEADD(second, 60, 0), 108)

    
    
53. on January 26, 2009 at 5:35 pm | Reply mark o keeffe

    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

    
    
54. on February 2, 2009 at 7:42 pm | Reply Liat

    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?

    
    
55. on February 9, 2009 at 9:18 pm | Reply husam

    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

    
    
56. on February 10, 2009 at 7:13 am | Reply Imran Mohammed

    @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.

    
    
57. on February 11, 2009 at 7:10 am | Reply bghoh

    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

    
    
58. on February 11, 2009 at 9:19 am | Reply Imran Mohammed

    @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.

    
    
59. on February 11, 2009 at 9:39 am | Reply Vasant

    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

    
    
60. on February 11, 2009 at 3:23 pm | Reply Janardhan

    I need help with converting a number into the string (e.g. 100 to be ‘hundred’). I am working with SQL Server 2005..

    
    
61. on February 11, 2009 at 10:43 pm | Reply bghoh

    @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′);

    
    
62. on February 12, 2009 at 5:24 am | Reply bghoh

    @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),

    
    
63. on February 12, 2009 at 10:48 am | Reply Imran Mohammed

    @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.

    
    
64. on February 12, 2009 at 11:02 am | Reply Imran Mohammed

    @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.

    
    
65. on February 17, 2009 at 4:44 pm | Reply Purnima

    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…….

    
    
66. on February 18, 2009 at 11:41 am | Reply Imran Mohammed

    @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.

    
    
67. on February 25, 2009 at 10:48 am | Reply kumars

    How to write sql query monthname

    
    
68. on February 25, 2009 at 7:11 pm | Reply Milind

    How to convert 8.23 as 08.23?

    
    
69. on February 25, 2009 at 11:09 pm | Reply Brian Tkatch

    @kumars SELECT DATENAME(mm, CURRENT_TIMESTAMP)
    @Milind SELECT ‘0′ + CAST(‘8.23′ AS VARCHAR)

    
    
70. on February 26, 2009 at 11:12 am | Reply tejasnshah

    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

    
    
71. on March 30, 2009 at 10:08 pm | Reply Meghna

    You have saved my life so many times, I can pay anything you want. Anything! Thank again.

    
    
72. on March 31, 2009 at 5:57 am | Reply Soigpm papa

    Sir,

    you a saved my skin again.

    Come to myanmar we will treat you best.

    
    
73. on March 31, 2009 at 9:36 pm | Reply Bob

    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?

    
    
74. on May 7, 2009 at 9:03 pm | Reply pnormoyle

    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

    
    
75. on May 26, 2009 at 4:01 pm | Reply Raviteja

    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.”

    
    
76. on May 27, 2009 at 1:02 pm | Reply Sunil Kumar Pidugu

    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

    
    
    • on November 5, 2009 at 4:19 pm Umamahesh

      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

      
      
77. on June 2, 2009 at 12:24 pm | Reply jyothi

    Hi Sunil
    R u trying to export the data from the excel file or flat file?

    
    
78. on June 2, 2009 at 2:38 pm | Reply Pawan

    Thanks to Anand and Gram It Helped me a lot..thank you very much

    
    
79. on June 20, 2009 at 11:24 am | Reply Manu

    The query to convert varchar to integer helps
    but what we have to do for null values

    Thanks and Regards
    MaNu GoEl

    
    
80. on July 2, 2009 at 3:15 pm | Reply Idris Gani

    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

    
    
81. on July 25, 2009 at 1:17 am | Reply Ricardo Oliveira

    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

    
    
82. on September 9, 2009 at 7:00 pm | Reply Ashok

    i have an integer 24036 in mssql 2000, i need to convert it to month and year.(no date).

    Please give me the solution

    
    
83. on October 16, 2009 at 12:36 pm | Reply Maniraj

    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…

    
    
84. on October 16, 2009 at 7:05 pm | Reply Brian Tkatch

    @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.

    
    
85. on October 31, 2009 at 9:49 am | Reply Shalini

    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.

    
    
86. on November 2, 2009 at 8:41 pm | Reply Brian Tkatch

    @Shalini

    To search for a list like that, you can use Dynamic SQL.