StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your compare will work, but it will be slow because the dates are converted to a string for each row. To efficiently compare two time parts, try:</p> <pre><code>declare @first datetime set @first = '2009-04-30 19:47:16.123' declare @second datetime set @second = '2009-04-10 19:47:16.123' select (cast(@first as float) - floor(cast(@first as float))) - (cast(@second as float) - floor(cast(@second as float))) as Difference </code></pre> <p>Long explanation: a date in SQL server is stored as a floating point number. The digits before the decimal point represent the date. The digits after the decimal point represent the time.</p> <p>So here's an example date:</p> <pre><code>declare @mydate datetime set @mydate = '2009-04-30 19:47:16.123' </code></pre> <p>Let's convert it to a float:</p> <pre><code>declare @myfloat float set @myfloat = cast(@mydate as float) select @myfloat -- Shows 39931,8244921682 </code></pre> <p>Now take the part after the digit, i.e. the time:</p> <pre><code>set @myfloat = @myfloat - floor(@myfloat) select @myfloat -- Shows 0,824492168212601 </code></pre> <p>Convert it back to a datetime:</p> <pre><code>declare @mytime datetime set @mytime = convert(datetime,@myfloat) select @mytime -- Shows 1900-01-01 19:47:16.123 </code></pre> <p>The 1900-01-01 is just the "zero" date; you can display the time part with convert, specifying for example format 108, which is just the time:</p> <pre><code>select convert(varchar(32),@mytime,108) -- Shows 19:47:16 </code></pre> <p>Conversions between datetime and float are pretty fast, because they're basically stored in the same way.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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