Compare a date string to datetime in SQL Server?

Question

In SQL Server I have a DATETIME column which includes a time element.

Example:

'14 AUG 2008 14:23:019'

What is the best method to only select the records for a particular day, ignoring the time part?

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

DECLARE  @p_date DATETIME
SET      @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

SELECT *
FROM   table1
WHERE  column_datetime = @p_date

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.

---

Update Clarified example to be more specific.

---

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

@Jorrit: WHERE (date>'20080813' AND date<'20080815') will return the 13th and the 14th.

@wearejimbo: Close, but no cigar! 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.)

Answer 1

Good point about the index in the answer you accepted.

Still, if you really search only on specific DATE or DATE ranges often, then the best solution I found is to add another persisted computed column to your table which would only contain the DATE, and add index on this column:

ALTER TABLE "table1" 
    ADD "column_date" AS CONVERT(DATE, "column_datetime") PERSISTED

Add index on that column:

CREATE NONCLUSTERED INDEX "table1_column_date_nu_nci"
ON  "table1" ( "column_date" ASC )
GO

Then your search will be even faster:

DECLARE  @p_date DATE
SET      @p_date = CONVERT( DATE, '14 AUG 2008', 106 )

SELECT   *
FROM     table1
WHERE    column_date = @p_date

Answer 2

I normally convert date-time to date and compare them, like these:

SELECT 'Same Date' WHERE CAST(getDate() as date) = cast('2/24/2012 2:23 PM' as date)

or

SELECT 'Same Date' WHERE DATEDIFF(dd, cast(getDate() as date), cast('2/24/2012 2:23 PM' as date)) = 0

Answer 3

Something like this?

SELECT  *
FROM    table1
WHERE   convert(varchar, column_datetime, 111) = '2008/08/14'

Answer 4

Technique 1:

 DECLARE @p_date DATETIME
 SET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

 SELECT  *
 FROM    table1
 WHERE   column_datetime >= @p_date
 AND     column_datetime < DATEADD(d, 1, @p_date)

The advantage of this is that it will use any index on 'column_datetime' if it exists.

Answer 5

The best way is to simply extract the date part using the SQL DATE() Function:

SELECT * 
FROM table1
WHERE DATE(column_datetime) = @p_date;

Answer 6

Technique 2:

DECLARE @p_date DATETIME
SET     @p_date = CONVERT( DATETIME, '14 AUG 2008', 106 )

SELECT  *
FROM    table1
WHERE   DATEDIFF( d, column_datetime, @p_date ) = 0

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.