Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    Unanswered: Trouble putting a zero at the front of a single digit variable?

    Hi Everyone,

    I am retrieving the current month and setting a variable accordingly.

    Code:
    DECLARE @currMonthNum INT
    
    SET @currMonthNum = DATEPART(MM, GETDATE())
    My challenge is that at a later point I want to use the @currMonthNum value to 'build' a date in the style yyyymmdd and the variable is only one digit for the months January to September (e.g.: 1 to 9). For the months January to September I need to pad the variable with an extra zero (e.g.: 01 to 09).

    Here is my attempt to do this...!

    Code:
    IF @currMonthNum IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
    	SET @currMonthNum = '0' + CAST(@currMonthNum AS nvarchar)
    Unfortunately this does not give the desired result of two digits, however ironically if I add a + '0' to the end I get 10 to 90! It is like the 0 at the front is simply being ignored.

    I have also tried setting the @currMonthNum variable to an nvarchar type before performing the character addition as well attempting to use the CONCAT command (however I don't think that CONCAT can be used when setting a variable).

    If anybody can share some wisdom on how to display a zero in front of the months that require it I will greatly appreciate it.

    Kind Regards,

    David

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First off, SQL has strong data types for a reason. Treating a DATE part as anything other than a DATE is fraught with problems and it will never be easy or a good idea. The same thing happens when people represent currency using the REAL data type, or anything that has a defined type as a VARCHAR. You certainly can do it, but it is almost never a good idea.

    With that said, this particular problem isn't really hard to solve. Check out:
    Code:
    SELECT GetDate() AS thisDate
    ,  Year(GetDate()) AS thisYear
    ,  Month(GetDate()) AS thisMonth
    ,  Day(GetDate()) AS thisDay
    ,  Year(GetDate()) * 10000 + Month(GetDate()) * 100 + Day(GetDate())
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Or, you could use the convert function:
    Code:
    select convert(char(8), getdate() , 112)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can use Convert() if you have a DATETIME, but not with the INT that the user has proposed.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ahh, then if he is on a SQL 2012 or later, he can use the DATEFROMPARTS function.

    (but still, dates should be stored as dates, and not numbers)

Posting Permissions

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