SELECT shows different amount of rows

Question

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?

There was no user interaction with this table today (other than running the two SELECT's)
There is no with (nolock) on the SELECT query
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
There is no stored procedure or job to delete any rows

score 4 · Answer 1 · answered Jan 06 '19 at 13:51

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:

How does date math work in SQL Server?

SELECT shows different amount of rows

1 Answers1