0

I have a job which each day (at 6:01pm) copies 516 rows from a table and seeds them with tomorrow's date

INSERT MYDB.MARKET_DATA(
            MARKET_DATE, 
            CURRENCY_BASE, 
            CURRENCY_TERM, 
            PERIOD, 
            MID)
             (
               SELECT 
                  DATEADD(hh, 6, GETDATE()), 
                  MARKET_DATA.CURRENCY_BASE, 
                  MARKET_DATA.CURRENCY_TERM, 
                  MARKET_DATA.PERIOD, 
                  MARKET_DATA.MID
               FROM MYDB.MARKET_DATA with (nolock)
               WHERE cast(MARKET_DATE as date)=cast (getDate() as date)
             )

We also check that the right number of rows are copied by looking at the number of rows for the last 7 days

SELECT count(*)
,CAST(market_date AS DATE)
FROM mydb.market_data
WHERE market_date > dateadd(day, - 7, getdate())
GROUP BY CAST(market_date AS DATE)
ORDER BY CAST(market_date AS DATE) DESC

We ran this SELECT query twice today. At 10am in the morning it returned 517 rows for today, and at 1pm it returns 516 rows for today.

What could possibly account for this discrepancy?

  1. There was no user interaction with this table today (other than running the two SELECT's)
  2. There is no with (nolock) on the SELECT query
  3. Note that there is a with (nolock) on the INSERT/SELECT, because the rows are updated throughout the day with latest information, and I don't want the INSERT/SELECT to fail on any rows that are being updated at the precise moment that it is being executed
  4. There is no stored procedure or job to delete any rows
3
  • 1
    Have you tried to cast getdate() as date?
    – McNets
    Jan 6, 2019 at 11:41
  • No. But which getDate() are you referring to - the one in the INSERT/SELECT or the one in the SELECT?
    – gordon613
    Jan 6, 2019 at 11:54
  • I suggest you list out the 516 rows with a suitable order by and inspect them and your solution may become clearer.
    – Nick.Mc
    Jan 6, 2019 at 12:21

1 Answer 1

4

What you're running into is probably a misunderstanding with how DATEADD works.

If you run these queries, you'll see what I mean:

DECLARE @getdate DATETIME = '2019-01-06 10:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

SET @getdate = '2019-01-06 01:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

Subtracting 7 days doesn't bring you to the start of seven days ago. It brings you to 7 days ago, but to the hour of the datetime value you passed in.

If you need to flatten a datetime to the beginning of the day, you need to do something like this.

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), 0);

You can check out my answer here for more information:

5
  • Thank you @Erik Darling for your answer! May I suggest, however, that my omission to flatten the datetime of 7 days ago, would not affect the problem with the rows for today which are being grouped anyway by a (cast getDate() as Date) which would flatten the dates correctly. At worst, the query would omit some records from 7 days ago.
    – gordon613
    Jan 6, 2019 at 15:11
  • @gordon613 The issue is with your where clause. Jan 6, 2019 at 15:12
  • I am sorry I don't follow. Even if I corrected my WHERE clause to WHERE market_date > DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), -7) the records that would be returned for the last six days would be identical to before the correction. The only change would be those records seven days ago. And my problem was with today's records
    – gordon613
    Jan 6, 2019 at 15:34
  • 5
    @gordon613 okay, cool, post the sample data that's giving you incorrect results, along with expected results. Jan 6, 2019 at 15:39
  • This kind of error only happens every couple of months or so, (although this is the first time I have seen the manifestation described in my question) and it is too late now for me to post the incorrect results, as they have been somehow corrected - as per my question - I will try and catch it next time!
    – gordon613
    Jan 6, 2019 at 16:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.