search date field by partial date?

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    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.

  • Jeff Moden

    SSC Guru

    Points: 995468

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    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)

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    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

  • Jeff Moden

    SSC Guru

    Points: 995468

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Eirikur Eiriksson

    SSC Guru

    Points: 182400

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

  • mick burden

    SSCarpal Tunnel

    Points: 4432

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

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Thanks for the alt code sscrazy.

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

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