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

Date can be compared in sqlserver using string comparision: e.g.

DECLARE @strDate VARCHAR(15)
SET @strDate ='07-12-2010'


SELECT * FROM table
WHERE CONVERT(VARCHAR(15),dtInvoice, 112)>= CONVERT(VARCHAR(15),@strDate , 112)

Answer 2

SELECT * FROM tablename
WHERE CAST(FLOOR(CAST(column_datetime AS FLOAT))AS DATETIME) = '30 jan 2012'

Answer 3

SELECT CONVERT(VARCHAR(2),DATEPART("dd",doj)) + 
    '/' + CONVERT(VARCHAR(2),DATEPART("mm",doj)) + 
    '/' + CONVERT(VARCHAR(4),DATEPART("yy",doj)) FROM emp

Answer 4

In sqlserver

DECLARE @p_date DATE

SELECT * 
FROM table1
WHERE column_dateTime=@p_date

In C# Pass the short string of date value using ToShortDateString() function. sample: DateVariable.ToShortDateString();

Answer 5

DECLARE @Dat

SELECT * 
FROM Jai
WHERE                                                                                                          
CONVERT(VARCHAR(2),DATEPART("dd",Date)) +'/'+                                                              
             CONVERT(VARCHAR(2),DATEPART("mm",Date)) +'/'+              
                     CONVERT(VARCHAR(4), DATEPART("yy",Date)) = @Dat

Answer 6

How to get the DATE portion of a DATETIME field in MS SQL Server:

One of the quickest and neatest ways to do this is using

DATEADD(dd, DATEDIFF( dd, 0, @DAY ), 0)

It avoids the CPU busting "convert the date into a string without the time and then converting it back again" logic.

It also does not expose the internal implementation that the "time portion is expressed as a fraction" of the date.

Get the date of the first day of the month

DATEADD(dd, DATEDIFF( dd, -1, GetDate() - DAY(GetDate()) ), 0)

Get the date rfom 1 year ago

DATEADD(m,-12,DATEADD(dd, DATEDIFF( dd, -1, GetDate() - DAY(GetDate()) ), 0))