CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
tesmp
tesmp

Reputation: 631

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

Upvotes: 1

Views: 170

Answers (2)

Rahul
Rahul

Reputation: 13056

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

Upvotes: 2

kprobst
kprobst

Reputation: 16651

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)

Upvotes: 3

Related Questions