Results 1 to 2 of 2

Thread: Format string in T-SQL

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Format string in T-SQL

    Hi Friends,

    I have to convert my docnum field wiht existing docnum and adding month and year from docdate field. for example i have invoice no 0859 it should be converted into like 0406-0859 where first two digists are month where other two digits are year.

    i worte code but it converted like 42006-0859 but i need exact like mentioned as above.

    my tsql codes are


    set debtorsopeninvoices.docnum=replace(docnum,docnum,str(month(docdate))+str(year(docdate))+'-'+ docnum)

  2. #2
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: Format string in T-SQL

    Unfortunately SQL Server does not have a Format function similar to the function in VB, but you can easily create a simplified Format function in SQL Server. I have created a simple Format function that takes two parameters, @Expression and @Format. All it does is to put @Expression in the right part of @Format. If @Expression is '23' and @Format is '00000' the return value will be '00023'.

    Here is the function:
    Code:
    CREATE FUNCTION fn_Format (@Expression varchar(8000),@Format varchar(8000))  
    RETURNS varchar(8000) AS  
    BEGIN 
    declare @ret varchar(8000), @temp_expr varchar(8000)
    
    if @Expression is null or @Format is null
    begin
    	set @ret=NULL
    end
    else
    begin
    	if len(@Expression)>=len(@Format)
    	begin
    		set @ret=@Expression
    	end
    	else
    	begin
    		set @ret=substring(@Format,1,len(@Format)-len(@Expression))+@Expression
    	end
    end
    return @ret

    END

    Used in your TSQL statement:
    Code:
    et debtorsopeninvoices.docnum=dbo.fn_Format(convert(varchar(2),month(docdate)),'00')+right(convert(varchar(4),year(docdate)),')),2)+'-'+ docnum
    Keep in mind that if you execute the UPDATE for a row more than once, the datpart will be added to docnum over and over again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width