Post reply

Getting data from 7 days back to today, but what about from just the 7th day back? (no other days included)

  • October 26, 2017 at 2:37 pm

    #340989

    DATETIME = DATEADD(DAY,-6,@Yesterday)
    DATETIME = CAST(GETDATE() AS DATE)

    So that gets me data from a week ago, to today. But I am having trouble writing so I only get the 7th day (back) of data.

    DATETIME =DATEADD(DAY,DATEDIFF(DAY,-6,GETDATE()),0) ??

  • October 26, 2017 at 2:41 pm

    #1965395

    chef423 - Thursday, October 26, 2017 2:37 PM

    DATETIME = DATEADD(DAY,-6,@Yesterday)
    DATETIME = CAST(GETDATE() AS DATE)

    So that gets me data from a week ago, to today. But I am having trouble writing so I only get the 7th day (back) of data.

    DATETIME =DATEADD(DAY,DATEDIFF(DAY,-6,GETDATE()),0) ??

    This doesnt error but I return no data
    DATETIME = DATEADD(DAY,DATEDIFF(DAY,-6,GETDATE()),-6)

  • October 26, 2017 at 3:04 pm

    #1965403

    This has to be one of the vaguest and most unclear questions I've ever seen on this site. But is this what you're getting at?

    SELECT [mycrap]
    FROM [mytable]
    WHERE
        [mydate] >= DATEADD(DAY,-7,CAST(GETDATE() AS DATE)) AND
        [mydate] < DATEADD(DAY,-6,CAST(GETDATE() AS DATE))

    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • October 26, 2017 at 3:07 pm

    #1965406

    autoexcrement - Thursday, October 26, 2017 3:04 PM

    This has to be one of the vaguest and most unclear questions I've ever seen on this site. But is this what you're getting at?

    SELECT [mycrap]
    FROM [mytable]
    WHERE
        [mydate] >= DATEADD(DAY,-7,CAST(GETDATE() AS DATE)) AND
        [mydate] < DATEADD(DAY,-6,CAST(GETDATE() AS DATE))

    That was right in front me!

    Thanks!

  • October 26, 2017 at 3:12 pm

    #1965409

    Never mind...  misread the previous post.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • October 26, 2017 at 5:41 pm

    #1965428

    Or:

    SELECT [mycrap]
    FROM [mytable]
    WHERE
        CAST([mydate] AS DATE) = DATEADD(DAY,-7,CAST(GETDATE() AS DATE))

  • October 26, 2017 at 5:52 pm

    #1965431

    ^ but isn't that "non-sargable"?

    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • October 26, 2017 at 5:59 pm

    #1965432

    autoexcrement - Thursday, October 26, 2017 5:52 PM

    ^ but isn't that "non-sargable"?

    Actually, it is sargable.  One of the quirks of the date/time data types.

  • October 26, 2017 at 6:05 pm

    #1965434

    I always learn something here! Never fails.

    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Viewing 9 posts - 1 through 8 (of 8 total)

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