SQL SERVER – Convert Text to Numbers (Integer) – CAST and CONVERT

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)

About these ads

425 thoughts on “SQL SERVER – Convert Text to Numbers (Integer) – CAST and CONVERT

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

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

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

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

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

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

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

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

    from “001.000″ to “1″ ?

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

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

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

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

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

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

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

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

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

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

  19. 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!

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

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

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

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

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

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

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

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

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

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

  30. 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!

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

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

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

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

  35. 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)

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

  37. 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?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  59. 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;

  60. 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))

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

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

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

  64. 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;

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

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

  67. 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?

  68. 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)

  69. i thing it must be something like this for example

    “SELECT MAX ( SELECT CONVERT(INT, code) FROM customer) AS PLITHOS FROM customer “;

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

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

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

  73. 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)

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

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

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

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

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

  79. 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?

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

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

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

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

  84. 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?

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

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

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

  88. Is there system Tables/views/sps/fucntions to list the all date time format styles which is an input for convert function?

  89. 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)

  90. 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?

  91. 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?

  92. 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?

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

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

  95. hi sir,

    I learned lot about sql queries from your website. it’s simple and useful.

    with regards,
    A.Moorthy

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

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

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

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

  100. 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 !!!

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

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

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

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

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

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

  107. 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,

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

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

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

  111. Madhivanan,

    Are you saying to use dateadd instead of convert? Yes I am trying to convert from seconds to valid dates.

    Thank you

    Doug

  112. 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???

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

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

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

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

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

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

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

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

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

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

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

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

  124. select cast(‘john’ as int)
    –gives me error
    –Conversion failed when converting the varchar value ‘John’ –to data type int

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

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

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

  128. the following sql is not working.give a solution

    Select isnull(max(convert(int,substring(BookCode,2,4))),’S’)
    from BookMaster

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

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

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

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

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

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

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

  136. 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?

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

  138. 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?

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

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

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

  142. 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?

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

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

    • 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

  145. 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’)

  146. 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?

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

  148. 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!

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

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

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

  152. 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)

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

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

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

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

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

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

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

  160. 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
    ————-

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

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

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

  164. 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')

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

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

  167. 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?

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

  169. 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?

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

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

  172. 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′)

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

  174. Hello. I require help on a conversion from varchar to numeric.
    I have nulls, 0.00, and numbers such as (134.75) to express negative values.
    They are all in varchar data type. When trying various convert/cast functions, I am still not able to convert these figures into a numeric. I’ve tried removing the parentheses but to no avail.

  175. I have a 14 digit varchar UPC code for example 00012580632101. I use either convert or case and it cuts off my leading zero digits. I need these to come out as they are relevant to the UPC number I need to use. How can I use cast or convert and keep my leading zeros?

  176. Hi everybody!

    I’m pretty noobie in SQL Server, and I’m using the 2008 R2 version. I’m quite used to MSAccess and i have problems due the difference in functions names between the apps.

    I have a column that holds time value, and it is a varchar. The data stored in it is like 10.58.03 and it is a time. I got how to gets only the minute [SUBSTRING(CONVERT(VARCHAR([TRN_TIME],108),4,2)] but it is a text, and I need to check if it is greater than 30, to group it.

    But it is returned as String, and even addind CONVERT(INT,…) on it, it keeps returning string.

    Can someone help me? Thanks in advance

  177. Pingback: SQL SERVER – Weekly Series – Memory Lane – #037 | Journey to SQL Authority with Pinal Dave

  178. How to change Interger to substring .
    Example:
    SELECT e.empid , e.empname, empaddress,co.CountryName,s.states,c.city,gender
    FROM empdetail e INNER JOIN Country co
    ON e.empid = co.Country_id JOIN state s
    ON co.Country_id = s.State_id
    join citys c
    ON s.State_id=c.city_id
    i need to apply the substring for empid..
    pls any one can apply…

  179. Hi….
    i executed below commands successfully….
    create table DMART (ID int identity(1,1), PARTICULARS VARCHAR(20), QUANTITY SQL_VARIANT, RATE DECIMAL(20,1), VALUE DECIMAL (20,2))
    SELECT * FROM DMART
    INSERT INTO DMART (PARTICULARS, QUANTITY, RATE) VALUES(‘REDGRAM’,2.008, 70.50), (‘SUGAR’, 1.002, 35.50)
    INSERT INTO DMART (PARTICULARS, QUANTITY, RATE) VALUES(‘RAVA’,1.048, 30.50), (‘CHOPPING’, 1, 139.00),
    (‘BATH MESH’, 1, 21.00), (‘BELT LADIES’, 1, 99.00), (‘BRITANNIA G’, 2, 26.00), (‘BROOKE BOND’, 1, 107.00),
    (‘CHICKEN MASALA’, 1, 18.00), (‘MAAZA’, 1, 28.00)

    My doubt is…
    unable to perform below task….
    update DMART set VALUE=QUANTITY*RATE

    The error is:
    Msg 260, Level 16, State 3, Line 1
    Disallowed implicit conversion from data type sql_variant to data type decimal, table ‘DMART’, column ‘QUANTITY’. Use the CONVERT function to run this query.

    plz help me…

    • Hi Kranti,
      you need to use convert function for updating the query.
      use below query it will help you …
      update DMART SET VALUE=CONVERT(DECIMAL(20,3),QUANTITY)*RATE

  180. Hi , I want convert alphanumeric to numeric. the conversion should be like this , example input is abt538z , the output should be . 122053826(a =1 ,b=2,c=3……z=26)……….. any body tell me the code………..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s