Post reply

search date field by partial date?

  • January 10, 2015 at 4:20 pm

    #293632

    on one of my tables I've a field set as smalldatetime, which is populated by various dates ranging between 01/01/2000 and 01/01/2015. What I'm after is being able to pass in a partial date such as 03/2011 and get a record set of all the dates in the table that falls in March 2011, prefably distinct. Can anyone help please.

  • January 10, 2015 at 9:18 pm

    #1769272

    Assuming that a parameter called @pSomeDate is a VARCHAR parameter to take the '03/2011', the following should do it.

    WITH

    cteStartOfMonth AS

    (

    SELECT StartOfMonth = DATEADD(mm,CAST(LEFT(@pSomeDate,2) AS INT)-1,RIGHT(@pSomeDate,4))

    )

    SELECT yada, yada, yada

    FROM dbo.YourTable yt

    CROSS JOIN cteStartOfMonth som

    WHERE yt.YourDateColumn >= som.StartOfMonth

    AND yt.YourDateColumn < DATEADD(mm,1,som.StartOfMonth)

    ;

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

  • January 11, 2015 at 2:45 am

    #1769282

    That's great Jeff, based on your example I ended up with, which works well. Is there a way I can return distinct values in ChartDate ?

    declare @pSomeDate as varchar(20)

    set @pSomeDate = '04/2011';

    WITH

    cteStartOfMonth AS

    (

    SELECT StartOfMonth = DATEADD(mm,CAST(LEFT(@pSomeDate,2) AS INT)-1,RIGHT(@pSomeDate,4))

    )

    SELECT recid, weeknumber, title,ChartDate

    FROM music.dbo.AllTimeTopFortySingles yt

    CROSS JOIN cteStartOfMonth som

    WHERE yt.ChartDate >= som.StartOfMonth

    AND yt.ChartDate < DATEADD(mm,1,som.StartOfMonth)

  • January 11, 2015 at 5:52 am

    #1769288

    I've come up with the following. Thanks for putting me in the right direction

    declare @pSomeDate as varchar(20)

    set @pSomeDate = '04/2011';

    WITH

    cteStartOfMonth AS

    (

    SELECT StartOfMonth = DATEADD(mm,CAST(LEFT(@pSomeDate,2) AS INT)-1,RIGHT(@pSomeDate,4))

    )

    SELECT distinct ChartDate

    FROM music.dbo.AllTimeTopFortySingles yt

    CROSS JOIN cteStartOfMonth som

    WHERE yt.ChartDate >= som.StartOfMonth

    AND yt.ChartDate < DATEADD(mm,1,som.StartOfMonth)

    order by ChartDate

  • January 11, 2015 at 10:07 am

    #1769311

    Glad it worked out, Mick. Just to be sure, do you understand how/why it works?

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

  • January 12, 2015 at 12:11 am

    #1769403

    Slightly simpler solution for SQL Server 2008 and later, produces the same results

    😎

    declare @pSomeDate as varchar(20) = '04/2011';

    SELECT

    recid

    ,weeknumber

    ,title

    ,ChartDate

    FROM music.dbo.AllTimeTopFortySingles yt

    WHERE yt.ChartDate >= CONVERT(SMALLDATETIME,'01/' + @pSomeDate,103)

    AND yt.ChartDate < DATEADD(MM,1,CONVERT(SMALLDATETIME,'01/' + @pSomeDate,103));

    Quick thought, adding an index on the date column including the output column would be very beneficial.

    CREATE NONCLUSTERED INDEX [Index Name] ON [TABLE_SCHEMA].

    ([DATE COLUMN] ASC) INCLUDE ([OUTPUT COLUMNS});

  • January 12, 2015 at 2:33 am

    #1769424

    Thanks Jeff, after studying it I've worked out how it works.

  • January 12, 2015 at 2:34 am

    #1769425

    Thanks for the alt code sscrazy.

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

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