CHECK CONSTRAINT FAIL with query using GETDATE()

• craig.dixon

  Say Hey Kid

  Points: 688

  More actions

  January 7, 2014 at 6:08 am

  #284104

  Hey Everyone,

  I hope your all enjoying the New Year 🙂 I'm having an issue with some CHECK CONSTRAINTS and table partitioning that I'm hoping someone could help me out with.

  I have 2 CHECK CONSTRAINTS and 2 tables and 1 view:

  View: The_Data

  Table 1: Current_Data

  Table 2: January_Data

  CONSTRAINT 1

  Name: CK_Current_Date_Range

  Definition: [[Generation_Date]>='2014-01-01 12:00:00:000']

  CONSTRAINT 2

  Name: CK_January_Date_Range

  Definition: [[Generation_Date]<='2014-01-06 15:00:00:000']

  When I query the data using a datetime I enter it all works fine, for example, if I want to see the data for the last 2 hours:

  SELECT *

  FROM The_Data

  WHERE (Generation_Date > '2014-01-07 10:55:00:000')

  From the Execution Plan I can see that an Index Scan was only performed on the Current_Data table, so that's great... however. When I use DATEADD() it doesn't work as expected, for example, using the query below:

  SELECT *

  FROM The_Data

  WHERE (Generation_Date > DATEADD(hour, - 2, GETDATE()))

  This time the Execution Plan shows that Index Scans are being performed on both the Current_Data and January_Data tables :ermm: The DATEADD() results are in the correct format i.e. yyyy-mm-dd hh:mm:ss:mmm so I cant see an issue there. Could this be to do with using a CHECK CONSTRAINT with a function?

  I'd really appreciate any help

  Thanks!