Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: 23:59:59 Of Last Sunday using GetDate

    Hello

    I have the following code, which retrieves data for last Sunday, however I have noticed that the GetDate Function returns

    2014-01-05 00:00:00.000

    Code: DATEADD(wk, DATEDIFF(wk, 7, getdate()), 6)

    I do however need to collect data for the entire day on the sunday and therefore need to amend my SQL so I can retrieve records up until 23:59:59 on this particular day

    Is anyone able to help me on my way to achieving this?


    Please note that my entire SQL will be collecting data for the beginning of the financial year to end tme of last sunday
    Full Code:

    between dateadd(mm,3,dateadd(yy, datediff(yy,0,dateadd(mm,-3,getdate())),0)) and DATEADD(wk, DATEDIFF(wk, 7, getdate()), 6)
    (I have tried googling, but cannot seem to piece the SQL string together)

    Kind Regards
    Helen

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your best answer would be to alter the criteria so that it checks for "less than Monday". This works in all cases regardless of the time precision (different time datatypes like SMALLDATETIME, DATETIME, and DATETIME2) would require different calculations to find the last moment of the day.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2014
    Posts
    1

    Add a day, subtract a second

    dateadd( dd, 1, dateadd( s, -1, DATEADD(wk, DATEDIFF(wk, 7, getdate()), 6)))

  4. #4
    Join Date
    Dec 2011
    Posts
    82
    Hello

    Thank you very much for your suggestion....This really works for me

    Thanks
    Helen

  5. #5
    Join Date
    Dec 2011
    Posts
    82
    Hello Pat

    Thanks for the suggestion, this would work for me and at least I would be capturing all of my data for the Sunday

    pdross2000 has aslo suggested the following, which takes a second of the time to count to 23:59:59

    dateadd( dd, 1, dateadd( s, -1, DATEADD(wk, DATEDIFF(wk, 7, getdate()), 6)))

    Thanks
    Helen

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I have the following code, which retrieves data for last Sunday, however I have noticed that the getdate() Function returns

    2014-01-05 00:00:00.000*<<

    Stop using this 1970's Sybase/UNIX function. We now have the ANSI/ISO Standard CURRENT_TIMESTAMP.

    >> I do however need to collect data for the entire day on the Sunday and therefore need to amend my SQL so I can retrieve records [sic] up until 23:59:59 on this particular day <<

    Nope, just use a DATE data type: CAST (CURRENT_TIMESTAMP AS DATE) and not worry about the TIME fields. Oh, the term "record" is not part of SQL or RDBMS;the term "field" refers to {YEAR, MONTH, DAY, HOUR, MINUTE, SECOND} parts of a temporal data element.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •