Select full month number (with 0)

  • Hi,

    I use

    DATEPART(month, myDate)

    or

    MONTH(myDate)

    the resut is: 2 (if myDate is 2013.02.03). I would like return: 02

    Do you have any solution for this?

    Thans!

  • do you want this

    select RIGHT('0'+CONVERT(VARCHAR(2),month(GETDATE())),2)

  • Thank you very much!

  • Or simply:

    SELECT CONVERT(char(2), myDate, 1)

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (3/1/2013)


    Or simply:

    SELECT CONVERT(char(2), myDate, 1)

    I don't think will quite work Scott. The OP said they are using month(MyDate) which will return an int. Casting a single digit int to a char(2) will result in 'x '.

    declare @MyDate datetime = '20130301'

    select convert(char(2), month(@MyDate), 1)

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (3/1/2013)


    ScottPletcher (3/1/2013)


    Or simply:

    SELECT CONVERT(char(2), myDate, 1)

    I don't think will quite work Scott. The OP said they are using month(MyDate) which will return an int. Casting a single digit int to a char(2) will result in 'x '.

    declare @MyDate datetime = '20130301'

    select convert(char(2), month(@MyDate), 1)

    Since myDate is a datetime, you can use a straight CONVERT, no MONTH() at all. My point was, given that, MONTH() was wasted effort that forced even further wasted effort: once you use MONTH(), then you have to use RIGHT() and concatenation.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (3/1/2013)


    Sean Lange (3/1/2013)


    ScottPletcher (3/1/2013)


    Or simply:

    SELECT CONVERT(char(2), myDate, 1)

    I don't think will quite work Scott. The OP said they are using month(MyDate) which will return an int. Casting a single digit int to a char(2) will result in 'x '.

    declare @MyDate datetime = '20130301'

    select convert(char(2), month(@MyDate), 1)

    Since myDate is a datetime, you can use a straight CONVERT, no MONTH() at all. My point was, given that, MONTH() was wasted effort that forced even further wasted effort: once you use MONTH(), then you have to use RIGHT() and concatenation.

    Oh sure, make it more simple, faster and easier to understand. Somehow I missed you not using MONTH. Sometimes I seriously need a "post checker" before I hit that button. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • ScottPletcher (3/1/2013)


    Or simply:

    SELECT CONVERT(char(2), myDate, 1)

    I'm not poking holes here so go easy. Considering another conversation about not so obvious code, wouldn't the concatenation method of adding a zero be more correct insofar as the easy readability you were speaking of?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (3/1/2013)


    ScottPletcher (3/1/2013)


    Or simply:

    SELECT CONVERT(char(2), myDate, 1)

    I'm not poking holes here so go easy. Considering another conversation about not so obvious code, wouldn't the concatenation method of adding a zero be more correct insofar as the easy readability you were speaking of?

    This also relies on silent truncation of the full string. Something I have seen people complain about in T-SQL.

  • In this case, the silent truncation is actually a desirable thing. I just think it odd that Scott would favor this particular method because someone not-in-the-know would have to make a trip to BOL to figure out what date format #1 was whereas the other method is absolutely clear. Scott had opposed a method I used for something similar because it required a bit of extra knowledge of the developer just like this code of his does.

    That, notwithstanding, I'd use Scott's method instead of the other method simply because it's almost twice as fast. A simple comment could be included in Scott's code if someone felt an explanation was necessary.

    Performance Proof:

    DECLARE @Bitbucket CHAR(2);

    SET STATISTICS TIME ON;

    SELECT TOP 2000000

    @Bitbucket = CONVERT(CHAR(2), GETDATE(), 1)

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    SELECT TOP 2000000

    @Bitbucket = RIGHT('0'+CONVERT(VARCHAR(2),MONTH(GETDATE())),2)

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    SET STATISTICS TIME OFF;

    Results on my 11 year old desktop.

    SQL Server Execution Times:

    CPU time = 563 ms, elapsed time = 706 ms.

    SQL Server Execution Times:

    CPU time = 954 ms, elapsed time = 971 ms.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This method requires that the developer understand the date format code and that silent truncation is taking place. Like I said, how many times have we seen people complain about it occuring thinking that it should throw an error.

  • Lynn Pettis (3/3/2013)


    This method requires that the developer understand the date format code and that silent truncation is taking place. Like I said, how many times have we seen people complain about it occuring thinking that it should throw an error.

    While I agree, that's why comments were invented. I'm one of those folks that will go for the highest performance possible (2nd only to accuracy) even if it does require a bit of the arcane. How many people have you and I taught the Tally Table to or how to find the first day of a month? Tell me that neither of those are arcane knowledge. Neither usage even shows up in BOL. Both require arcane knowledge easily made up for by a simple, well placed, well written comment.

    And, that's the point I was trying to make with Scott on another thread. We do all sorts of stuff that requires not-so-obvious knowledge, so go for the speed and write a comment. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • P.S. Let's hope the Microsoft never fixes the "silent truncation" problem. I rely on it a whole lot! 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (3/3/2013)


    Lynn Pettis (3/3/2013)


    This method requires that the developer understand the date format code and that silent truncation is taking place. Like I said, how many times have we seen people complain about it occuring thinking that it should throw an error.

    While I agree, that's why comments were invented. I'm one of those folks that will go for the highest performance possible (2nd only to accuracy) even if it does require a bit of the arcane. How many people have you and I taught the Tally Table to or how to find the first day of a month? Tell me that neither of those are arcane knowledge. Neither usage even shows up in BOL. Both require arcane knowledge easily made up for by a simple, well placed, well written comment.

    And, that's the point I was trying to make with Scott on another thread. We do all sorts of stuff that requires not-so-obvious knowledge, so go for the speed and write a comment. 🙂

    We are in full agreement! I have learned the same from you. God enough just isn't good enough anymore. It has to scale, and beyond what one may think occur.

  • Jeff Moden (3/3/2013)


    In this case, the silent truncation is actually a desirable thing. I just think it odd that Scott would favor this particular method because someone not-in-the-know would have to make a trip to BOL to figure out what date format #1 was whereas the other method is absolutely clear. Scott had opposed a method I used for something similar because it required a bit of extra knowledge of the developer just like this code of his does.

    That, notwithstanding, I'd use Scott's method instead of the other method simply because it's almost twice as fast. A simple comment could be included in Scott's code if someone felt an explanation was necessary.

    Performance Proof:

    DECLARE @Bitbucket CHAR(2);

    SET STATISTICS TIME ON;

    SELECT TOP 2000000

    @Bitbucket = CONVERT(CHAR(2), GETDATE(), 1)

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    SELECT TOP 2000000

    @Bitbucket = RIGHT('0'+CONVERT(VARCHAR(2),MONTH(GETDATE())),2)

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    SET STATISTICS TIME OFF;

    Results on my 11 year old desktop.

    SQL Server Execution Times:

    CPU time = 563 ms, elapsed time = 706 ms.

    SQL Server Execution Times:

    CPU time = 954 ms, elapsed time = 971 ms.

    I think that's the cleanest and fastest code to do this task, since a format code is needed either way.

    But yes, I've repeatedly stated I'm against using format codes for literal date values. I've also given the reason: 'YYYYMMDD[ hh:mm:ss.sss]' format always works, regardless of date and/or language settings.

    Given that, I think introducing a format code adds unnecessary chances for errors. Say I copy code with a literal date of '07/04/2012', format code 103, from a non-English source to use in my English code. I think that adds work: the person reading that code later must: (1) look the code up; (2) not bother to look it up and perhaps misinterpret the value, because they're used to mm/dd/yyyy; (3) much less likely, but more ominously, accidentally or not, change the code to the more-familiar 101.

    No format code, otoh, no such issues are even possible. Thus, I still strongly recommend that format codes not be used for literal dates. I'm not so sure why you insist on using codes for literal dates, but I prefer not to.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply