Post reply

question on date functions: dateadd(qq,datediff(qq,-1,GETDATE()),-1)

  • June 16, 2011 at 7:09 am

    #240017

    Can someone explain to me what is happening with these four date function calls.

    1) dateadd(qq,datediff(qq,-1,GETDATE()),-1)

    2) dateadd(qq,datediff(qq,1,GETDATE()),0)

    3) dateadd(yy,datediff(yy,-1,GETDATE()),-1)

    4) dateadd(yy,datediff(yy,1,GETDATE()),0)

    Thanks

  • June 16, 2011 at 8:04 am

    #1339735

    The DATEADD() function adds or subtracts a specified time interval from a date.

    Syntax

    DATEADD(datepart,number,date)

    qq = quarter

    yy = Year

    in your question the date field is representative as 0 or -1, in the datepart syntax SQL server relates 0 as 1900-01-01 00:00:00.000

    and -1 as 1 day before 0 1899-12-31 00:00:00.000

    so for question 1 you can sum up as follows

    select dateadd(qq,446,'1899-12-31 00:00:00.000') is the same as select dateadd(qq,datediff(qq,-1,GETDATE()),-1)

    446 is derived by the datediff(qq,-1,GETDATE()) section of the function in english there are 466 quaters bettween 1899-12-31 00:00:00.000

    and now

    Hope this helps

    ***The first step is always the hardest *******

  • June 16, 2011 at 8:10 am

    #1339749

    sorry there are 446 quaters bettween 1899-12-31 00:00:00.000

    and now not 466

    ***The first step is always the hardest *******

  • June 19, 2011 at 7:14 am

    #1340858

    Consider your first statement:

    dateadd(qq,datediff(qq,-1,GETDATE()),-1)

    1. GETDATE() gets you current date

    2. 0 here represents system start date which is 1900-01-01 00:00:00.000

    3. -1 here represents a year before 1900 i.e 1899

    4. qq is the abbr. for date part Quarter of any year

    So, datediff(qq,-1,GETDATE()) returns the number of quarters that come between -1 ( = the year 1899 ) and the current year ( = 2011). The final answer you get is 446.

    Now, the outer part of the statement will be like dateadd(qq,446,-1). This implies you want to add 446 quarters to the year 1899 ( = -1 ) which should return you some date this year.

  • June 19, 2011 at 8:30 pm

    #1340949

    ankit.shukla1105 (6/19/2011)

    -1 here represents a year before 1900 i.e 1899

    Not really. It's precisely 1 day before the first day of 1900. Yes, it occurs in 1899 but I don't want anyone walking away thinking that it represents a 4 digit year only. -1 is the same as 1899-12-31. 🙂

    --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 5 posts - 1 through 4 (of 4 total)

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