I have a large table (45M rows) that I need to combine a date field and a time field into a DATETIME field. The SQL statement is sound in that it works on smaller sets, but larger sets seem to be giving me issues.
When I use larger sets to update (60+ days) I get the message that a string conversion to date time has failed. If you look at the query, I have removed any chance of invalid dates or times from being included in the update statement. Furthermore, I can lower the record count (shorten the number of days in the where clause) and it runs without issue. I can repeat that for the exact same date ranges of the original query and it works fine. (albeit in multiple queries to accomplish this)
I have seen similar behavior in the past on other update statements and never really looked too deeply into it. It appears that when a large number of rows are targeted the database scans outside of the where clause before it executes. The query appears to be evaluating records that are not within the range of the where clause and causing a failure.
Is this possible? How do I limit the update statement and make it stay within the bounds of the where clause?
Any help in my understanding of the issue will be greatly appreciated!
Sample below (NOTE: I cut it short on the in clause, no need to clog up space here just to list off all the possible valid times. The statement ends with ...,'2359')
set locdatetime = convert(varchar, locationdate, 101) + ' ' + substring(locationtime, 1, 2) + ':' + substring(locationtime, 3,2)
where locationdate >= '10/1/2013'
and locationdate < '11/15/2013'
and locdatetime is null
and locationtime in('0000',