Remove time from DateTime sql server 2005

I need date part from date开发者_JAVA百科time. in format of "dd-mm-yyyy"

I have tried follwoing

Query:

select Convert(varchar(11), getdate(),101)

Output:

01/11/2011

Query

SELECT cast(floor(cast(GETDATE() as float)) as datetime)

Output

2011-01-11 00:00:00.000

Query:

SELECT 
    CONVERT(VARCHAR(MAX),DATENAME(DD,GETDATE())) + '-' + 
    CONVERT(VARCHAR(MAX),DATEPART(MONTH,GETDATE())) + '-' + 
    CONVERT(VARCHAR(MAX),DATENAME(YYYY,GETDATE())) `

Output:

11-1-2011 i.e. "d-m-yyyy"

I required output in "dd-mm-yyyy" format.

SELECT CONVERT(VARCHAR(10),GETDATE(),105)

Try:

SELECT convert(varchar, getdate(), 105)

More here.

Here you can find some examples how to do this: http://blog.pengoworks.com/index.cfm/2009/1/9/Useful-tips-and-tricks-for-dealing-with-datetime-in-SQL

Using the CONVERT function "works" but only if you're comparing strings with strings. To compare dates effectively, you really need to keep the SMALLDATETIME data type strongly typed on both side of the equation (ie "="). Therefore 'apros' comment above is really the best answer here because the blog mentioned has the right formulas to use to strip off the time component by "flattening" it to midnight (ie 12:00:00) via rounding and any date column in SQL Server 2005 will always default to 12:00:00 if the date is given without a time.

This worked for me ...

select dateadd(day, datediff(day, '20000101', @date), '20000101')