Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The answer is <code>SELECT DATEADD(ms, -3, '2008-01-24')</code>, the explanation is below.</p> <p>From <a href="http://musingmarc.blogspot.com/2006/07/more-on-dates-and-sql.html" rel="noreferrer">Marc's blog</a>:</p> <blockquote> <p>But wait, Marc... you said you like to use <code>BETWEEN</code>, but that query doesn't have one... that's because <code>BETWEEN</code> <a href="http://www.techonthenet.com/sql/between.php" rel="noreferrer">is inclusive</a>, meaning it includes the end-points. If I had an Order that was due at midnight of the first day of the next month it would be included. So how do you get the appropriate value for an end-of-period? It's most certainly <strong>NOT</strong> by using date-parts to assemble one (but is you must, please remember that it's 23:59:59.997 as a maximum time... don't forget the milliseconds). To do it right, we use the incestuous knowledge that Microsoft SQL Server <code>DATETIME</code> columns have <em>at most</em> a 3 millisecond resolution (something that is not going to change). So all we do is subtract 3 milliseconds from any of those end-of-period formulas given above. For example, the last possible instant of yesterday (local time) is:</p> <pre><code> SELECT DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0)) </code></pre> <p>So to do the orders due this month as a <code>BETWEEN</code> query, you can use this:</p> <pre><code> SELECT [ID] FROM [dbo].[Orders] WHERE [ShipDue] BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GetUTCDate()), 0) AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GetUTCDate()) + 1, 0)) </code></pre> <p>Remember, always make sure that you do math against input parameters, <strong>NOT</strong> columns, or you will kill the <a href="http://www.databasejournal.com/features/mssql/article.php/1436301" rel="noreferrer">SARG</a>-ability of the query, which means indexes that might have been used aren't.</p> </blockquote>
    singulars
    1. This table or related slice is empty.
    1. POHow do I get the last possible time of a particular day
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USbdukes
    1. USbdukes
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSorry but this selected the latest time of day before the date I wanted..... rather than that particular day
      singulars
      1. PO
      1. UStest
    2. COThis is great! A really, really easy way to get the end of the day.
      singulars
      1. PO
      1. USPandincus
 

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