SQL code

  • Hi All,

    Can someone help me interpret what this code is doing.

    open_date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) AND DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) - '00:00:01'

    and is there another way to re-write it.

    EO

  • eobiki10 (12/5/2013)


    Hi All,

    Can someone help me interpret what this code is doing.

    open_date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) AND DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) - '00:00:01'

    and is there another way to re-write it.

    EO

    It looks to me like it's part of a WHERE clause that filters for rows where open_date is in the current month including time of day.

  • DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

    Is getting the first day of previous month.

    DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

    Is getting the first day of current month.

    - '00:00:01'

    Is substracting one second to get only values from previous month because BETWEEN limits are inclusive.

    There are ways to rewrite the code, but this might be the best in terms of performance and flexibility. It's basically adding months to a base date (zero or 1900-01-01) to eliminate days and time. You could use the same functionality for other datetime parts.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (12/5/2013)


    DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

    Is getting the first day of previous month.

    DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

    Is getting the first day of current month.

    - '00:00:01'

    Is substracting one second to get only values from previous month because BETWEEN limits are inclusive.

    There are ways to rewrite the code, but this might be the best in terms of performance and flexibility. It's basically adding months to a base date (zero or 1900-01-01) to eliminate days and time. You could use the same functionality for other datetime parts.

    DOH! I typed current month. :blush: Luis is right - it's previous month. Thanks for catching it, Luis.

Viewing 4 posts - 1 through 3 (of 3 total)

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