Post reply

using dateadd function

  • October 15, 2012 at 2:01 am

    #263286

    Hi All

    I'm new @ SQL server. i need to select record that are within 30 days of this given date '20110420'.

    Plz help.

  • October 15, 2012 at 2:10 am

    #1549013

    select criteria from yourtable where datecolumn < dateadd(dd, 30, '20110420')

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • October 15, 2012 at 2:33 am

    #1549016

    Henrico Bekker (10/15/2012)

    select criteria from yourtable where datecolumn < dateadd(dd, 30, '20110420')

    What do you mean by "within"?

    Is it within 30 days before the day?

    WHERE datecolumn BETWEEN '20110420' AND DATEADD(dd, -30, '20110420')

    Is it within 30 days after the day?

    WHERE datecolumn BETWEEN '20110420' AND DATEADD(dd, 30, '20110420')

    Is it within 30 days before and 30 days after?

    WHERE datecolumn BETWEEN DATEADD(dd, -30, '20110420') AND DATEADD(dd, 30, '20110420')

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • October 16, 2012 at 3:17 pm

    #1549802

    I've always avoided using the BETWEEN operator on datetime columns, because most of what I work with should not get any overlap at all between one "window" of time and the next.

    So, instead of

    --Smaller value MUST come first. Otherwise it returns no results

    WHERE datecolumn BETWEEN DATEADD(dd, -30, '20110420') AND '20110420'

    Which is the equivalent of

    --Includes 2011-04-20 12:00 AM

    WHERE datecolumn >= DATEADD(dd, -30, '20110420') AND datecolumn <= '20110420'

    I would use

    --Does not include 2011-04-20 12:00 AM

    WHERE datecolumn >= DATEADD(dd, -30, '20110420') AND datecolumn < '20110420'

    This way, I can do a very similar function if I want to get the NEXT set of 30 days, and I will get absolutely no overlaps.

  • October 16, 2012 at 9:25 pm

    #1549852

    Select columnname1, columnname2

    FROM

    tablename

    WHERE datecolumnname BETWEEN DATEADD(dd, -30, '20110420') AND '20110420'

  • October 17, 2012 at 12:35 am

    #1549880

    Hi 🙂

    thanx guys my Report is working nicely now.

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

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