Select records between a date range

  • Heh... Mom always said, "Never run with scissors"... being in a hurry on code has the same effect on careers.

    Anyway, glad you figured it out... and thank for the feedback.

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

  • OP, when you have time, read this

    http://sql-server-performance.com/fk_datetime.asp


    Madhivanan

    Failing to plan is Planning to fail

  • declare @DateFrom DateTime = CONVERT(DATETIME, '01/01/2014', 103)

    declare @DateTo DateTime = CONVERT(DATETIME, '01/03/2014', 103)

    ;WITH CTE(dt)

    AS

    (

    Select @DateFrom

    Union All

    Select DATEADD(d,1,dt)FROM CTE

    Where dt<@DateTo

    )

    select DayName,CONVERT(varchar(50), dt, 103)as Date from (select 'Saturday' as DayName,dt from CTE

    where DATENAME(dw,dt)In('Saturday') group by dt

    Union

    select 'Sunday'as DayName,dt from CTE

    where DATENAME(dw,dt)In('Sunday'))as result order by dt

    Reference : http://aj-learning.com/blog/get-weekend-date-sql-server-specific-date-range/

  • ajlearningcom (4/18/2014)


    declare @DateFrom DateTime = CONVERT(DATETIME, '01/01/2014', 103)

    declare @DateTo DateTime = CONVERT(DATETIME, '01/03/2014', 103)

    ;WITH CTE(dt)

    AS

    (

    Select @DateFrom

    Union All

    Select DATEADD(d,1,dt)FROM CTE

    Where dt<@DateTo

    )

    select DayName,CONVERT(varchar(50), dt, 103)as Date from (select 'Saturday' as DayName,dt from CTE

    where DATENAME(dw,dt)In('Saturday') group by dt

    Union

    select 'Sunday'as DayName,dt from CTE

    where DATENAME(dw,dt)In('Sunday'))as result order by dt

    Reference : http://aj-learning.com/blog/get-weekend-date-sql-server-specific-date-range/

    Please read the following article for why you should generally avoid rCTEs that "count" by 1 like yours does. There are several much more efficient ways to do the same thing.

    [font="Arial Black"]Hidden RBAR: Counting with Recursive CTE's[/font][/url]

    --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 4 posts - 16 through 18 (of 18 total)

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