SQL Drop Time in DateTime

2009-01-10 10:00:00.000

2009-01-10 12:00:00.000

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

declare @d datetime
set @d = getdate()

select dateadd(d, datediff(day, 0, @d), 0)

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

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

Select * From SomeTable Where (DateDiff(d, FirstDate, SecondDate) = 0)

convert(char(10),getdate(),101)

[A] JOIN [B] ON SQLMenace([A].datetime) = SQLMenace([B].datetime)

(Where SQLMenace() is the operation he described *grin*)

[A] JOIN [B] ON [A].datetime >= SQLMenace([B].datetime) AND [A].datetime < SQLMenace([B].datetime)

create table #datedemo(
 id         integer primary key identity(1,1) not null
,date       datetime not null
,dateint    int
)

insert into #datedemo (date)
          select '2009-Jan-29 02:12:00'
union all select '2009-Jan-29 16:22:00'
union all select '2009-Jan-30 00:32:00'
union all select '2009-Jan-30 13:42:00'
union all select '2009-Jan-31 01:52:00'
union all select '2009-Feb-01 12:02:00'

update #datedemo set dateint = floor(cast(date as float))

select id,date,dateint,cast(dateint as datetime) from #datedemo
where dateint = floor(cast(cast('2009-Jan-30' as datetime)as float))

drop table #datedemo

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime StripTime(SqlDateTime date)
    {
        return date.IsNull ? SqlDateTime.Null : new SqlDateTime(date.DayTicks, 0);
    }
};

CONVERT( smalldatetime, @myDate, 103 )