Extract Date Portion Only From DateTime Field In SQL Server 2008 I use this to take out only the date piece Cast(field as date).  For example, if I use the below garbage DDL

  Create Table #Bbb
  (
    id int
    ,datetime1 datetime
  )

  Insert Into #Bbb Values
  ('1', '2016-03-01 17:35:19.000')
  ,('2','2016-03-01 18:44:15.000')
  ,('3','2016-03-05 18:58:05.000')
  ,('4','2016-03-05 19:52:27.000')


I run this query it will only give me the 2 rows where the date is equal to 03/01/2016:

Select *
FROM #Bbb
WHERE CAST(datetime1 As Date) = '2016-03-01'


Now the question being, how would I do the same in a SQL Server 2000 instance, since Cast(datetime1 As Date) does not exist?
You can check for the daily boundaries i.e. between the begining of the day at 00:00 and the begining of the next day at 00:00.

Query:

DECLARE @date datetime = '20160301'

SELECT *
FROM #Bbb
WHERE datetime1 &gt;= @date AND datetime1 &lt; DATEADD(day, 1, @date)
;


Or this WHERE clause for dates on a given day or later:

WHERE datetime1 &gt;= @date


Output:

id  | datetime1
1   | 2016-03-01 17:35:19.000
2   | 2016-03-01 18:44:15.000


You can also remove the TIME part (set it to 00:00:00.000) with this query:

DECLARE @date datetime = getdate()
SELECT @date, DATEADD(day, DATEDIFF(day, 0, @date), 0);


However using functions will limit index usage.

Ouput:

datetime                | date
2016-03-09 14:40:25.080 | 2016-03-09 00:00:00.000