Post reply

I'm Having Trouble Parsing This Date Calculation Expression

  • January 9, 2017 at 6:08 pm

    #333387

    I inherited a bunch of code from a predecessor at my job, and I'm trying to go through it all to make sure I understand what it's doing. There's this one line in particular that returns the first day of the current month. It works fine, but it's not clear to me how it's getting there. The code looks like this:

    Select DATEADD(month, DATEDIFF(MONTH, 0, getdate()), 0)

    ;

    When I run just the DATEDIFF(MONTH, 0, getdate()) part of the code, I get 1404, which I completely don't understand the meaning of. I've tried to look up what happens when you use a 0 in the second parameter when SQL is expecting a date value, but I cannot find anything.

    When I run DATEADD(month, 1404, 0), I get '2017-01-01 00:00:00.000', which is the value that I want; but how SQL processes these integers in a date datatype environment is not clear to me.

    Can somebody break this down for me?

    Thank you!

  • January 9, 2017 at 8:01 pm

    #1921519

    It has to do with INTEGER MATH.

    The number "0" in the integer "date serial number" of the first of January, 1900,

    The number 1404 is the number of [font="Arial Black"]whole [/font]month's since the first of January, 1900.

    Adding 1404 whole months back to the first of January, 1900, will give you the first of the given month.

    --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)
    Intro to Tally Tables and Functions

  • January 10, 2017 at 11:20 am

    #1921684

    Brilliant! Problem solved.

    Thank you

  • January 10, 2017 at 1:07 pm

    #1921737

    You could replace the zero (in both places) with any other date or value that can be implicitly converted into a datetime. It's helpful to prevent errors when dealing with smaller time parts or to make it easier for other folks to read if they're not used to the formula.

    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

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

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