Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2002
    Posts
    229

    Unanswered: Adding a leading character to a string

    I'm looking for a string function (or any other quick way) for adding a leading zero to make a string two characters long. For example, if the input is '12' the result will be '12' but if the input is '1', the result will be '01'.

    It's actually about making a month number two characters long but I've understood there is no way to make Datediff() to fix it for me.

    I thought there might be a string function for this, like there is in many programming languages, but I can't find anything in BOL. I want to keep it simple, because it will be used in a Select and a Group By for aggregating data based on time intervals (year + month).

    And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT Convert(CHAR(7), GetDate(), 121)
    Use the date expresion of your choice instead of GetDate()

    -PatP

  3. #3
    Join Date
    Jan 2006
    Posts
    3
    I use this
    Code:
    create function dbo.FN_INT_TOSTRING(@codigo int, @length int) returns varchar(10)
    begin
        declare @res varchar(10)
        set @res = cast(@codigo as varchar)
        while len(@res) < @length
        begin
          set @res = '0' + @res
        end
        return @res
    end

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    What about a generic function like:

    Code:
    Right('0' + cast(@MyInt as varchar(2)), 2)
    But Pat really has the best answer here.

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Nov 2005
    Location
    Copenhagen, Denmark
    Posts
    5
    Thanks everyone. Simpler than I first thought! I'll play with it at work on Monday morning (9 PM over here now).

    PS. Sorry for replying from another alias - I now realize I'm logged on as Coolberg from my work and as Nabucco from my home computer. I'll correct that. ;-)
    DS.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So finding an answer at 21:00 on Friday night doesn't inspire you to run right down to the office to try it ? Well, what kind of geek are you anyway ? Next thing you know, you'll be telling us that you're going to enjoy a cold beer and a warm bed!

    -PatP

  7. #7
    Join Date
    Dec 2003
    Posts
    61

    Leading zero (Month)

    select convert(varchar(2),getdate(),101)

  8. #8
    Join Date
    Jul 2002
    Posts
    229
    > Next thing you know, you'll be telling us that you're going to enjoy a cold beer and a warm bed!

    I missed the beer; the beer shop here closes at 6 PM ;-)

  9. #9
    Join Date
    Jul 2002
    Posts
    229
    Quote Originally Posted by forXLDB
    select convert(varchar(2),getdate(),101)
    I'll test this one tomorrow.

    By the way, SELECT Convert(CHAR(7), GetDate(), 121) didn't work.

    I tried Right('0' + cast(@MyInt as varchar(2)), 2) today, it worked fine.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    waddyamean it didn't work...of course it worked....read the hint sticky at the top of the forum
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2002
    Posts
    229
    The problem is, I'm doing a (for example)

    select convert(varchar(2),datepart(mm,'2006-01-30'),101)

    where you'll still get "1" since datepart() returns "1" , not "01".

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select right(convert(char(7),yourdate,120),2)
    Last edited by r937; 01-31-06 at 04:28.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Coolberg
    And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.
    select convert(char(7),yourdate,120)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2003
    Posts
    61

    Adding a leading character to a string

    Select convert(varchar(2),convert(datetime,'2006-01-30'),101)

  15. #15
    Join Date
    Jul 2002
    Posts
    229
    Thanks everybody!

Posting Permissions

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