<p>In SQL Server I have a <code>DATETIME</code> column which includes a time element.</p>

<p>Example:   </p>

<pre><code>'14 AUG 2008 14:23:019'
</code></pre>

<p>What is the <strong>best</strong> method to only select the records for a particular day, ignoring the time part?</p>

<p>Example:  (Not safe, as it does not match the time part and returns no rows)</p>

<pre><code>DECLARE  @p_date DATETIME
SET      @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

SELECT *
FROM   table1
WHERE  column_datetime = @p_date
</code></pre>

<p><em>Note: Given this site is also about jotting down notes and techniques you pick up and then forget, I'm going to post my own answer to this question as DATETIME stuff in MSSQL is probably the topic I lookup most in SQLBOL.</em></p>

<hr>

<p><strong>Update</strong> Clarified example to be more specific.</p>

<hr>

<p><strong>Edit</strong> Sorry, But I've had to down-mod WRONG answers (answers that return wrong results).</p>

<p>@Jorrit: <code>WHERE (date&gt;'20080813' AND date&lt;'20080815')</code> will return the 13th and the 14th.</p>

<p>@wearejimbo: <em>Close, but no cigar!</em> badge awarded to you. You missed out records written at 14/08/2008 23:59:001 to 23:59:999 (i.e. Less than 1 second before midnight.)</p>

[{'answer_id': 10826, 'author': 'Guy', 'author_id': 993, 'author_profile': 'https://Stackoverflow.com/users/993', 'pm_score': 6, 'selected': True, 'text': "<p>Technique 1:</p>\n\n<pre><code> DECLARE @p_date DATETIME\n SET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )\n\n SELECT  *\n FROM    table1\n WHERE   column_datetime &gt;= @p_date\n AND     column_datetime &lt; DATEADD(d, 1, @p_date)\n</code></pre>\n\n<p>The advantage of this is that it will use any index on 'column_datetime' if it exists.</p>\n"}, {'answer_id': 10828, 'author': 'Lars Mæhlum', 'author_id': 960, 'author_profile': 'https://Stackoverflow.com/users/960', 'pm_score': 1, 'selected': False, 'text': '<pre><code>SELECT  *\nFROM    table1\nWHERE   CONVERT(varchar(10),columnDatetime,121) = \n        CONVERT(varchar(10),CONVERT(\'14 AUG 2008\' ,smalldatetime),121)\n</code></pre>\n\n<p>This will convert the datatime and the string into varchars of the format "YYYY-MM-DD".</p>\n\n<p>This is very ugly, but should work</p>\n'}, {'answer_id': 10830, 'author': 'Guy', 'author_id': 993, 'author_profile': 'https://Stackoverflow.com/users/993', 'pm_score': 2, 'selected': False, 'text': "<p>Technique 2:</p>\n\n<pre><code>DECLARE @p_date DATETIME\nSET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )\n\nSELECT  *\nFROM    table1\nWHERE   DATEDIFF( d, column_datetime, @p_date ) = 0\n</code></pre>\n\n<p>If the column_datetime field is not indexed, and is unlikely to be (or the index is unlikely to be used) then using DATEDIFF() is shorter.</p>\n"}, {'answer_id': 10832, 'author': 'Yaakov Ellis', 'author_id': 51, 'author_profile': 'https://Stackoverflow.com/users/51', 'pm_score': 3, 'selected': False, 'text': "<p>Just compare the year, month and day values.</p>\n\n<pre><code>Declare @DateToSearch DateTime\nSet @DateToSearch = '14 AUG 2008'\n\nSELECT * \nFROM table1\nWHERE Year(column_datetime) = Year(@DateToSearch)\n      AND Month(column_datetime) = Month(@DateToSearch)\n      AND Day(column_datetime) = Day(@DateToSearch)\n</code></pre>\n"}, {'answer_id': 10833, 'author': 'GateKiller', 'author_id': 383, 'author_profile': 'https://Stackoverflow.com/users/383', 'pm_score': 2, 'selected': False, 'text': "<p>This function <strong>Cast(Floor(Cast(GetDate() As Float)) As DateTime)</strong> returns a datetime datatype with the time portion removed and could be used as so.</p>\n\n<pre><code>Select\n*\nTable1\nWhere\nCast(Floor(Cast(Column_DateTime As Float)) As DateTime) = '14-AUG-2008'\n</code></pre>\n\n<p>or</p>\n\n<pre><code>DECLARE  @p_date DATETIME\nSET      @p_date = Cast('14 AUG 2008' as DateTime)\n\nSELECT   *\nFROM     table1\nWHERE    Cast(Floor(Cast(column_datetime As Float)) As DateTime) = @p_date\n</code></pre>\n"}, {'answer_id': 10834, 'author': 'Jon Limjap', 'author_id': 372, 'author_profile': 'https://Stackoverflow.com/users/372', 'pm_score': 1, 'selected': False, 'text': "<p>I know this isn't exactly how you want to do this, but it could be a start:</p>\n\n<pre><code>SELECT *\nFROM (SELECT *, DATEPART(yy, column_dateTime) as Year, \n      DATEPART(mm, column_dateTime) as Month, \n      DATEPART(dd, column_dateTime) as Day \n      FROM table1)\nWHERE Year = '2008'\nAND Month = '8'\nAND Day = '14'\n</code></pre>\n"}, {'answer_id': 10835, 'author': 'ila', 'author_id': 1178, 'author_profile': 'https://Stackoverflow.com/users/1178', 'pm_score': 2, 'selected': False, 'text': "<p>Something like this?</p>\n\n<pre><code>SELECT  *\nFROM    table1\nWHERE   convert(varchar, column_datetime, 111) = '2008/08/14'\n</code></pre>\n"}, {'answer_id': 10844, 'author': 'vzczc', 'author_id': 224, 'author_profile': 'https://Stackoverflow.com/users/224', 'pm_score': 4, 'selected': False, 'text': '<p>In SQL Server 2008, you could use the new DATE datatype</p>\n\n<pre><code>DECLARE @pDate DATE=\'2008-08-14\'  \n\nSELECT colA, colB\nFROM table1\nWHERE convert(date, colDateTime) = @pDate  \n</code></pre>\n\n<p>@Guy. I think you will find that this solution scales just fine. Have a look at the <a href="http://cid-936d87e62ae20947.skydrive.live.com/self.aspx/vzczcPublic/Stackoverflow10825Query1.JPG" rel="noreferrer">query execution plan</a> of your original query.</p>\n\n<p>And for <a href="http://cid-936d87e62ae20947.skydrive.live.com/self.aspx/vzczcPublic/Stackoverflow10825Query2.JPG" rel="noreferrer">mine</a>:</p>\n'}, {'answer_id': 14581, 'author': 'Guy', 'author_id': 993, 'author_profile': 'https://Stackoverflow.com/users/993', 'pm_score': 2, 'selected': False, 'text': '<p><strong>How to get the DATE portion of a DATETIME field in MS SQL Server:</strong></p>\n\n<p>One of the quickest and neatest ways to do this is using</p>\n\n<pre><code>DATEADD(dd, DATEDIFF( dd, 0, @DAY ), 0)\n</code></pre>\n\n<p>It avoids the CPU busting "convert the date into a string without the time and then converting it back again" logic.</p>\n\n<p>It also does not expose the internal implementation that the "time portion is expressed as a fraction" of the date.</p>\n\n<p><strong>Get the date of the first day of the month</strong></p>\n\n<pre><code>DATEADD(dd, DATEDIFF( dd, -1, GetDate() - DAY(GetDate()) ), 0)\n</code></pre>\n\n<p><strong>Get the date rfom 1 year ago</strong></p>\n\n<pre><code>DATEADD(m,-12,DATEADD(dd, DATEDIFF( dd, -1, GetDate() - DAY(GetDate()) ), 0))\n</code></pre>\n'}, {'answer_id': 2271064, 'author': 'Jai', 'author_id': 274113, 'author_profile': 'https://Stackoverflow.com/users/274113', 'pm_score': 0, 'selected': False, 'text': '<pre><code>SELECT CONVERT(VARCHAR(2),DATEPART("dd",doj)) + \n    \'/\' + CONVERT(VARCHAR(2),DATEPART("mm",doj)) + \n    \'/\' + CONVERT(VARCHAR(4),DATEPART("yy",doj)) FROM emp\n</code></pre>\n'}, {'answer_id': 2271172, 'author': 'Jai', 'author_id': 274113, 'author_profile': 'https://Stackoverflow.com/users/274113', 'pm_score': 1, 'selected': False, 'text': '<pre><code>DECLARE @Dat\n\nSELECT * \nFROM Jai\nWHERE                                                                                                          \nCONVERT(VARCHAR(2),DATEPART("dd",Date)) +\'/\'+                                                              \n             CONVERT(VARCHAR(2),DATEPART("mm",Date)) +\'/\'+              \n                     CONVERT(VARCHAR(4), DATEPART("yy",Date)) = @Dat\n</code></pre>\n'}, {'answer_id': 2271336, 'author': 'van', 'author_id': 99594, 'author_profile': 'https://Stackoverflow.com/users/99594', 'pm_score': 2, 'selected': False, 'text': '<p>Good point about the index in the answer you accepted. </p>\n\n<p>Still, if you really search only on specific <code>DATE</code> or <code>DATE ranges</code> <strong>often</strong>, then the best solution I found is to add another persisted computed column to your table which would only contain the <code>DATE</code>, and add index on this column:</p>\n\n<pre><code>ALTER TABLE "table1" \n    ADD "column_date" AS CONVERT(DATE, "column_datetime") PERSISTED\n</code></pre>\n\n<p>Add index on that column:</p>\n\n<pre><code>CREATE NONCLUSTERED INDEX "table1_column_date_nu_nci"\nON  "table1" ( "column_date" ASC )\nGO\n</code></pre>\n\n<p>Then your search will be even faster:</p>\n\n<pre><code>DECLARE  @p_date DATE\nSET      @p_date = CONVERT( DATE, \'14 AUG 2008\', 106 )\n\nSELECT   *\nFROM     table1\nWHERE    column_date = @p_date\n</code></pre>\n'}, {'answer_id': 2570952, 'author': 'javad', 'author_id': 308248, 'author_profile': 'https://Stackoverflow.com/users/308248', 'pm_score': -1, 'selected': False, 'text': '<p>In sqlserver</p>\n\n<pre><code>DECLARE @p_date DATE\n\nSELECT * \nFROM table1\nWHERE column_dateTime=@p_date\n</code></pre>\n\n<p>In C#\nPass the short string of date value using ToShortDateString() function.\nsample:\n  DateVariable.ToShortDateString();</p>\n'}, {'answer_id': 4373895, 'author': 'Naresh Goradara', 'author_id': 284313, 'author_profile': 'https://Stackoverflow.com/users/284313', 'pm_score': 1, 'selected': False, 'text': "<p>Date can be compared in sqlserver using string comparision:\ne.g.</p>\n\n<pre><code>DECLARE @strDate VARCHAR(15)\nSET @strDate ='07-12-2010'\n\n\nSELECT * FROM table\nWHERE CONVERT(VARCHAR(15),dtInvoice, 112)&gt;= CONVERT(VARCHAR(15),@strDate , 112)\n</code></pre>\n"}, {'answer_id': 9061984, 'author': 'Michael Gasa', 'author_id': 1177766, 'author_profile': 'https://Stackoverflow.com/users/1177766', 'pm_score': 0, 'selected': False, 'text': "<pre><code>SELECT * FROM tablename\nWHERE CAST(FLOOR(CAST(column_datetime AS FLOAT))AS DATETIME) = '30 jan 2012'\n</code></pre>\n"}, {'answer_id': 9439193, 'author': 'Jaider', 'author_id': 480700, 'author_profile': 'https://Stackoverflow.com/users/480700', 'pm_score': 2, 'selected': False, 'text': "<p>I normally convert date-time to date and compare them, like these:</p>\n\n<pre><code>SELECT 'Same Date' WHERE CAST(getDate() as date) = cast('2/24/2012 2:23 PM' as date)\n</code></pre>\n\n<p>or</p>\n\n<pre><code>SELECT 'Same Date' WHERE DATEDIFF(dd, cast(getDate() as date), cast('2/24/2012 2:23 PM' as date)) = 0\n</code></pre>\n"}, {'answer_id': 30414576, 'author': 'user4932286', 'author_id': 4932286, 'author_profile': 'https://Stackoverflow.com/users/4932286', 'pm_score': 0, 'selected': False, 'text': '<p>The best way is to simply extract the date part using the SQL DATE() Function:</p>\n\n<pre><code>SELECT * \nFROM table1\nWHERE DATE(column_datetime) = @p_date;\n</code></pre>\n'}, {'answer_id': 35849283, 'author': 'Arun', 'author_id': 6030147, 'author_profile': 'https://Stackoverflow.com/users/6030147', 'pm_score': 0, 'selected': False, 'text': '<p>There are many formats for date in SQL which are being specified. Refer <a href="https://msdn.microsoft.com/en-in/library/ms187928.aspx" rel="nofollow">https://msdn.microsoft.com/en-in/library/ms187928.aspx</a></p>\n\n<p>Converting and comparing varchar column with selected dates.</p>\n\n<p>Syntax:</p>\n\n<pre><code>SELECT * FROM tablename where CONVERT(datetime,columnname,103) \n    between \'2016-03-01\' and \'2016-03-03\'\n</code></pre>\n\n\n\n<pre><code>In CONVERT(DATETIME,COLUMNNAME,103) "103" SPECIFIES THE DATE FORMAT as dd/mm/yyyy\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/10825', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/993/']