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

closed as off-topic by Erik Darling, Sean Gallardy, mustaccio, Mikael Eriksson, Paul White Jan 7 at 6:45

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, Sean Gallardy, mustaccio, Mikael Eriksson, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Have you tried to cast getdate() as date? – McNets Jan 6 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 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.McDermaid Jan 6 at 12:21
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:

  • 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 at 15:11
  • @gordon613 The issue is with your where clause. – Erik Darling Jan 6 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 at 15:34
  • 5
    @gordon613 okay, cool, post the sample data that's giving you incorrect results, along with expected results. – Erik Darling Jan 6 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 at 16:20

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