datetime conversions

Today i received a question about datetime conversions, because twitter is only allowing 160 characters in a reply i deceided to put it here.


The question was quite simple, what is the best way in SQL Server to receive only the datepart from a datetime variable. I got into it, and this is what it can be :

declare @dt1 datetime

      , @dt2 datetime

      , @dt3 date

     

set @dt1 = GETDATE()

set @dt2 = CONVERT(datetime, CONVERT(varchar(10), @dt1, 101))

set @dt3 = cast(@dt1 as date)

select @dt1, @dt2, @dt3

the @dt3 variable is a with a new type, date. Date is available in SQL Server 2008 and higher. If you can't use that.. use the @dt2 variable.