Drop Time in DateTime

Run this

SELECT DATEADD(dd, DATEDIFF(d, 0, Getdate()), 0)

change Getdate() to your column name to strip the date

BTW if you are doing a comparison it is better to do >= and < since it will perform better

You can use this syntax:-

CAST(date_field AS DATE) as column_name

Look here

You want the first one.

SELECT (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime))

Do not use any conversions to varchar, they are slow.

EDIT: @feihtthief - Here is a way to populate a table of dates (with 0 time) from a temp numbers table that is FAST. Edit it to save the numbers table if you want. They are handy as well.

DECLARE @DaysFromGoLive int                                                                                 SET @DaysFromGoLive = (SELECT (DATEDIFF(dd,'10/01/2007',GETDATE()) + 1)) /* Days from go live is the starting date of the table */ SELECT TOP 10950 --30 years of days                                                                                IDENTITY(INT,1,1) as N                                                                                 INTO #Numbers                                                                                               FROM Master.dbo.SysColumns sc1,                                                                                  Master.dbo.SysColumns sc2                                                                           CREATE TABLE [dbo].[TableOfDates](                                                                          [fld_date] [datetime] NOT NULL,                                                                            CONSTRAINT [PK_TableOfDates] PRIMARY KEY CLUSTERED                                                         (                                                                                                               [fld_date] ASC                                                                                            )WITH FILLFACTOR = 99 ON [PRIMARY]                                                                          ) ON [PRIMARY]                                                                                              INSERT INTO                                                                                                       dbo.TableOfDates                                                                                  SELECT                                                                                                            DATEADD(dd,nums.n - @DaysFromGoLive,CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)) as FLD_Date    FROM #Numbers nums                                                                                          SELECT MIN(FLD_Date) FROM dbo.TableOfDates                                                              SELECT MAX(FLD_Date) FROM dbo.TableOfDates                                                              DROP TABLE #Numbers