SQL Server: date conversion problem

I have a query like this:

SELECT     'Last 7 Days' AS Date_Range, CONVERT(smalldatetime, GETDATE()) - 6 AS Begin_Date, CONVERT(smalldatetime, GETDATE()) 
                      AS End_Date
FROM         sys.columns

produces output

Last 7 Days 2011-03-20 07:35:00 2011-03-26 07:35:00
Last 7 Days 2011-03-20 07:35:00 2011-03-26 07:35:00

How to get this?

Last 7 Days 2011-03-20 00:00:00 2011-03-26 00:00:00
Last 7 Days 2011-03-20 00:00:00 2011-03-26 00:00开发者_开发知识库:00

Do a DateAdd operation on the DT value you're getting back. This essentially removes the time component:

DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

You could just use date in the convert function instead of smalldatetime and then append "00:00:00" as string to the result.