<p>Here's what I use:</p>

<pre><code>SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME)
</code></pre>

<p>I'm thinking there may be a better and more elegant way.</p>

<p>Requirements:</p>

<ul>
<li>It has to be as fast as possible (the less casting, the better).</li>
<li>The final result has to be a <code>datetime</code> type, not a string.</li>
</ul>

[{'answer_id': 2781, 'author': 'Michael Stum', 'author_id': 91, 'author_profile': 'https://Stackoverflow.com/users/91', 'pm_score': 4, 'selected': False, 'text': "<p>Your <code>CAST</code>-<code>FLOOR</code>-<code>CAST</code> already seems to be the optimum way, at least on MS SQL Server 2005.</p>\n\n<p>Some other solutions I've seen have a string-conversion, like <code>Select Convert(varchar(11), getdate(),101)</code> in them, which is slower by a factor of 10.</p>\n"}, {'answer_id': 3130, 'author': 'Marek Grzenkowicz', 'author_id': 95, 'author_profile': 'https://Stackoverflow.com/users/95', 'pm_score': 5, 'selected': False, 'text': '<p>SQL Server 2008 has a new <a href="http://msdn.microsoft.com/en-us/library/bb630352.aspx" rel="noreferrer"><strong>date</strong> data type</a> and this simplifies this problem to:</p>\n\n<pre><code>SELECT CAST(CAST(GETDATE() AS date) AS datetime)\n</code></pre>\n'}, {'answer_id': 3154, 'author': 'Marek Grzenkowicz', 'author_id': 95, 'author_profile': 'https://Stackoverflow.com/users/95', 'pm_score': 4, 'selected': False, 'text': '<p>Itzik Ben-Gan in <a href="https://www.itprotoday.com/analytics-and-reporting/datetime-calculations-part-1" rel="nofollow noreferrer">DATETIME Calculations, Part 1</a> (SQL Server Magazine, February 2007) shows three methods of performing such a conversion (<strong>slowest to fastest</strong>; the difference between second and third method is small):</p>\n<pre><code>SELECT CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)\n\nSELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)\n\nSELECT CAST(CAST(GETDATE() - 0.50000004 AS int) AS datetime)\n</code></pre>\n<p>Your technique (casting to <em>float</em>) is suggested by a reader in the April issue of the magazine. According to him, it has performance comparable to that of second technique presented above.</p>\n'}, {'answer_id': 3696991, 'author': 'ErikE', 'author_id': 57611, 'author_profile': 'https://Stackoverflow.com/users/57611', 'pm_score': 8, 'selected': True, 'text': '<p><strong>SQL Server 2008 and up</strong></p>\n\n<p>In SQL Server 2008 and up, of course the fastest way is <code>Convert(date, @date)</code>. This can be cast back to a <code>datetime</code> or <code>datetime2</code> if necessary.</p>\n\n<p><strong>What Is Really Best In SQL Server 2005 and Older?</strong></p>\n\n<p>I\'ve seen inconsistent claims about what\'s fastest for truncating the time from a date in SQL Server, and some people even said they did testing, but my experience has been different. So let\'s do some more stringent testing and let everyone have the script so if I make any mistakes people can correct me.</p>\n\n<p><strong>Float Conversions Are Not Accurate</strong></p>\n\n<p>First, I would stay away from converting <code>datetime</code> to <code>float</code>, because it does not convert correctly. You may get away with doing the time-removal thing accurately, but I think it\'s a bad idea to use it because it implicitly communicates to developers that this is a safe operation and <strong>it is not</strong>. Take a look:</p>\n\n<pre><code>declare @d datetime;\nset @d = \'2010-09-12 00:00:00.003\';\nselect Convert(datetime, Convert(float, @d));\n-- result: 2010-09-12 00:00:00.000 -- oops\n</code></pre>\n\n<p>This is not something we should be teaching people in our code or in our examples online.</p>\n\n<p>Also, it is not even the fastest way!</p>\n\n<p><strong>Proof – Performance Testing</strong></p>\n\n<p>If you want to perform some tests yourself to see how the different methods really do stack up, then you\'ll need this setup script to run the tests farther down:</p>\n\n<pre><code>create table AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED);\ndeclare @d datetime;\nset @d = DateDiff(Day, 0, GetDate());\ninsert AllDay select @d;\nwhile @@ROWCOUNT != 0\n   insert AllDay\n   select * from (\n      select Tm =\n         DateAdd(ms, (select Max(DateDiff(ms, @d, Tm)) from AllDay) + 3, Tm)\n      from AllDay\n   ) X\n   where Tm &lt; DateAdd(Day, 1, @d);\nexec sp_spaceused AllDay;  -- 25,920,000 rows\n</code></pre>\n\n<p>Please note that this creates a 427.57&nbsp;MB table in your database and will take something like 15-30 minutes to run. If your database is small and set to 10% growth it will take longer than if you size big enough first.</p>\n\n<p>Now for the actual performance testing script. Please note that it\'s purposeful to not return rows back to the client as this is crazy expensive on 26 million rows and would hide the performance differences between the methods.</p>\n\n<p><strong>Performance Results</strong></p>\n\n<pre><code>set statistics time on;\n-- (All queries are the same on io: logical reads 54712)\nGO\ndeclare\n    @dd date,\n    @d datetime,\n    @di int,\n    @df float,\n    @dv varchar(10);\n\n-- Round trip back to datetime\nselect @d = CONVERT(date, Tm) from AllDay; -- CPU time = 21234 ms,  elapsed time = 22301 ms.\nselect @d = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 23031 ms, elapsed = 24091 ms.\nselect @d = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23782 ms, elapsed = 24818 ms.\nselect @d = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 36891 ms, elapsed = 38414 ms.\nselect @d = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 102984 ms, elapsed = 109897 ms.\nselect @d = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 103390 ms,  elapsed = 108236 ms.\nselect @d = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 123375 ms, elapsed = 135179 ms.\n\n-- Only to another type but not back\nselect @dd = Tm from AllDay; -- CPU time = 19891 ms,  elapsed time = 20937 ms.\nselect @di = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 21453 ms, elapsed = 23079 ms.\nselect @di = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23218 ms, elapsed = 24700 ms\nselect @df = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 29312 ms, elapsed = 31101 ms.\nselect @dv = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 64016 ms, elapsed = 67815 ms.\nselect @dv = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 64297 ms,  elapsed = 67987 ms.\nselect @dv = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 65609 ms, elapsed = 68173 ms.\nGO\nset statistics time off;\n</code></pre>\n\n<p><strong>Some Rambling Analysis</strong></p>\n\n<p>Some notes about this. First of all, if just performing a GROUP BY or a comparison, there\'s no need to convert back to <code>datetime</code>. So you can save some CPU by avoiding that, unless you need the final value for display purposes. You can even GROUP BY the unconverted value and put the conversion only in the SELECT clause:</p>\n\n<pre><code>select Convert(datetime, DateDiff(dd, 0, Tm))\nfrom (select \'2010-09-12 00:00:00.003\') X (Tm)\ngroup by DateDiff(dd, 0, Tm)\n</code></pre>\n\n<p>Also, see how the numeric conversions only take slightly more time to convert back to <code>datetime</code>, but the <code>varchar</code> conversion almost doubles? This reveals the portion of the CPU that is devoted to date calculation in the queries. There are parts of the CPU usage that don\'t involve date calculation, and this appears to be something close to 19875&nbsp;ms in the above queries. Then the conversion takes some additional amount, so if there are two conversions, that amount is used up approximately twice.</p>\n\n<p>More examination reveals that compared to <code>Convert(, 112)</code>, the <code>Convert(, 101)</code> query has some additional CPU expense (since it uses a longer <code>varchar</code>?), because the second conversion back to <code>date</code> doesn\'t cost as much as the initial conversion to <code>varchar</code>, but with <code>Convert(, 112)</code> it is closer to the same 20000&nbsp;ms CPU base cost.</p>\n\n<p>Here are those calculations on the CPU time that I used for the above analysis:</p>\n\n<pre><code>     method   round  single   base\n-----------  ------  ------  -----\n       date   21324   19891  18458\n        int   23031   21453  19875\n   datediff   23782   23218  22654\n      float   36891   29312  21733\nvarchar-112  102984   64016  25048\nvarchar-101  123375   65609   7843\n</code></pre>\n\n<ul>\n<li><p><em>round</em> is the CPU time for a round trip back to <code>datetime</code>.</p></li>\n<li><p><em>single</em> is CPU time for a single conversion to the alternate data type (the one that has the side effect of removing the time portion).</p></li>\n<li><p><em>base</em> is the calculation of subtracting from <code>single</code> the difference between the two invocations: <code>single - (round - single)</code>. It\'s a ballpark figure that assumes the conversion to and from that data type and <code>datetime</code> is approximately the same in either direction. It appears this assumption is not perfect but is close because the values are all close to 20000&nbsp;ms with only one exception.</p></li>\n</ul>\n\n<p>One more interesting thing is that the base cost is nearly equal to the single <code>Convert(date)</code> method (which has to be almost 0 cost, as the server can internally extract the integer day portion right out of the first four bytes of the <code>datetime</code> data type).</p>\n\n<p><strong>Conclusion</strong></p>\n\n<p>So what it looks like is that the single-direction <code>varchar</code> conversion method takes about 1.8&nbsp;μs and the single-direction <code>DateDiff</code> method takes about 0.18&nbsp;μs. I\'m basing this on the most conservative "base CPU" time in my testing of 18458&nbsp;ms total for 25,920,000 rows, so 23218&nbsp;ms / 25920000 = 0.18&nbsp;μs. The apparent 10x improvement seems like a lot, but it is frankly pretty small until you are dealing with hundreds of thousands of rows (617k rows = 1 second savings).</p>\n\n<p>Even given this small absolute improvement, in my opinion, the <code>DateAdd</code> method wins because it is the best combination of performance and clarity. The answer that requires a "magic number" of <code>0.50000004</code> is going to bite someone some day (five zeroes or six???), plus it\'s harder to understand.</p>\n\n<p><strong>Additional Notes</strong></p>\n\n<p>When I get some time I\'m going to change <code>0.50000004</code> to <code>\'12:00:00.003\'</code> and see how it does. It is converted to the same <code>datetime</code> value and I find it much easier to remember.</p>\n\n<p>For those interested, the above tests were run on a server where @@Version returns the following:</p>\n\n<blockquote>\n  <p>Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition on Windows NT 5.2  (Build 3790: Service Pack 2)</p>\n</blockquote>\n'}, {'answer_id': 17378887, 'author': 'srihari', 'author_id': 2518677, 'author_profile': 'https://Stackoverflow.com/users/2518677', 'pm_score': 2, 'selected': False, 'text': '<p>Please try:</p>\n\n<pre><code>SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]\n</code></pre>\n'}, {'answer_id': 26749689, 'author': 'user4217069', 'author_id': 4217069, 'author_profile': 'https://Stackoverflow.com/users/4217069', 'pm_score': 1, 'selected': False, 'text': '<p>SQL2005: I recommend cast instead of dateadd. For example,</p>\n<pre><code>select cast(DATEDIFF(DAY, 0, datetimefield) as datetime)\n</code></pre>\n<p>averagely about 10% <em>faster</em> on my dataset, than</p>\n<pre><code>select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0)\n</code></pre>\n<p>(and casting into smalldatetime was faster still)</p>\n'}]
['https://Stackoverflow.com/questions/2775', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/434/']