Databases
StackOverflow2013
Postsdbo
POSQL Server DateTime parameter 'rounding' warning
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server DateTime parameter 'rounding' warning
    primarykey
    data
    text
    <p>More of a warning than a question:</p> <p>We resolved a very puzzling bug this morning. We have a variety of reports that allow users to enter date ranges they want to run. The assumption is, if you ask for a report from 8/1/2010 to 8/10/2010 you meant to <em>include</em> 8/10/2010 so the end-date of the report isn't 8/10, it's something after that.</p> <p>It can't be 8/11/2010 becuase some of these reports rollup everything that happened during a day grouping them by that day which is at midnight, so a daily rollup would include an extra day - not what we wanted.</p> <p>To avoid the possibility of missing any items very very close to the end of the day, we computed the end date as 'one tick' less than tomorrow:</p> <pre><code>public static DateTime EndOfDay(DateTime day) { return day.Date.AddDays(1).AddTicks(-1); } </code></pre> <p>Internally this ends up something like 8/10/2010 12:59:59.9999PM</p> <p>Well, when you pass this DateTime to a DATETIME parameter in SQL Server it rounds the value UP to 8/11/2010 00:00:00! And since our query uses </p> <pre><code>DateField BETWEEN @FromDate AND @ToDate </code></pre> <p>instead of</p> <pre><code>DateField &gt;= @FromDate AND DateField &lt; @ToDate </code></pre> <p>We were seeing reports from 8/1/2010-8/10/2010 include items from 8/11/2010.</p> <p>The only way we discovered the real problem was by round-tripping the dates thru a string. DateTime.ToString() rounds too so we'd end up with 8/1/2010 12:59:59PM which SQL Server was happy with.</p> <p>So now our 'end of day' method looks like this:</p> <pre><code>public static DateTime EndOfDay(DateTime day) { // Cant' subtract anything smaller (like a tick) because SQL Server rounds UP! Nice, eh? return day.Date.AddDays(1).AddSeconds(-1); } </code></pre> <p>Sorry not a question - just thought someone might find it useful.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USn8wrl
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server DateTime parameter 'rounding' warning
      1. USJorriss
      1. VTFavorite
    1. COWouldn't a simpler solution be to change the query to use `DateField >= @FromDate AND DateField < @ToDate` - which is guaranteed to be correct?
      singulars
      1. POSQL Server DateTime parameter 'rounding' warning
      1. USEd Harper
    2. COIt would, but there are many many many sprocs to find that all work this way.
      singulars
      1. POSQL Server DateTime parameter 'rounding' warning
      1. USn8wrl
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload