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

I know this isn't exactly how you want to do this, but it could be a start:

SELECT *
FROM (SELECT *, DATEPART(yy, column_dateTime) as Year, 
      DATEPART(mm, column_dateTime) as Month, 
      DATEPART(dd, column_dateTime) as Day 
      FROM table1)
WHERE Year = '2008'
AND Month = '8'
AND Day = '14'

Answer 2

In SQL Server 2008, you could use the new DATE datatype

DECLARE @pDate DATE='2008-08-14'  

SELECT colA, colB
FROM table1
WHERE convert(date, colDateTime) = @pDate  

@Guy. I think you will find that this solution scales just fine. Have a look at the query execution plan of your original query.

And for mine:

Answer 3

SELECT  *
FROM    table1
WHERE   CONVERT(varchar(10),columnDatetime,121) = 
        CONVERT(varchar(10),CONVERT('14 AUG 2008' ,smalldatetime),121)

This will convert the datatime and the string into varchars of the format "YYYY-MM-DD".

This is very ugly, but should work

Answer 4

Just compare the year, month and day values.

Declare @DateToSearch DateTime
Set @DateToSearch = '14 AUG 2008'

SELECT * 
FROM table1
WHERE Year(column_datetime) = Year(@DateToSearch)
      AND Month(column_datetime) = Month(@DateToSearch)
      AND Day(column_datetime) = Day(@DateToSearch)

Answer 5

This function Cast(Floor(Cast(GetDate() As Float)) As DateTime) returns a datetime datatype with the time portion removed and could be used as so.

Select
*
Table1
Where
Cast(Floor(Cast(Column_DateTime As Float)) As DateTime) = '14-AUG-2008'

or

DECLARE  @p_date DATETIME
SET      @p_date = Cast('14 AUG 2008' as DateTime)

SELECT   *
FROM     table1
WHERE    Cast(Floor(Cast(column_datetime As Float)) As DateTime) = @p_date

Answer 6

There are many formats for date in SQL which are being specified. Refer https://msdn.microsoft.com/en-in/library/ms187928.aspx

Converting and comparing varchar column with selected dates.

Syntax:

SELECT * FROM tablename where CONVERT(datetime,columnname,103) 
    between '2016-03-01' and '2016-03-03'

In CONVERT(DATETIME,COLUMNNAME,103) "103" SPECIFIES THE DATE FORMAT as dd/mm/yyyy