Post reply

select YearMonth only

  • December 23, 2013 at 8:17 am

    #286498

    Hi,

    is there a way of selecting on year month only in sql when the format of the date passed is '2013-Jan'. I'm not sure at the moment if I have any control of the date format passed so wanted to explore the sql options.

    using sql server 2008 r2

    thanks,

  • December 23, 2013 at 8:25 am

    #1675630

    forgot to mention, the dates are stored in 2013-01-31 format.

  • December 23, 2013 at 8:30 am

    #1675632

    Is this something you are looking for?

    declare @InDate datetime = getdate()

    declare @sInDate varchar(20)

    set @sInDate = cast(YEAR(@InDate)as varchar) + '-' + left(CONVERT(varchar(20),@indate, 100),3)

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • December 23, 2013 at 8:36 am

    #1675633

    Yes. 2013-Jan will AUTOMATICALLY convert to a DATETIME datatype if played against a DATETIME column. For example...

    SELECT CAST('2013-Jan' AS DATETIME)

    Will return 2013-01-01 as a DATETIME. Your query could easily be ...

    SELECT whatever

    FROM dbo.SomeTable

    WHERE SomeDateColumn >= '2013-Jan'

    AND SomDateColumn < DATEADD(mm,1,'2013-Jan')

    ;

    Now all we have to hope is that your column is actually a DATETIME and not character based. If it is, you should fix it so that it is.

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

  • December 23, 2013 at 8:52 am

    #1675637

    cheers Jeff, it is a DATETIME column so I shall try your solutions now. Just a quick question would both examples SELECT any dates that fall with Jan 2013 (eg 1st - 31st)

  • December 23, 2013 at 9:06 am

    #1675641

    Yep that works, cheers Jeff

  • December 23, 2013 at 9:37 am

    #1675644

    mattech06 (12/23/2013)

    cheers Jeff, it is a DATETIME column so I shall try your solutions now. Just a quick question would both examples SELECT any dates that fall with Jan 2013 (eg 1st - 31st)

    No... only the second one does it properly.

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

Viewing 7 posts - 1 through 6 (of 6 total)

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