Remove time from SQL Server DateTime 2005

Question

I need a date-date from datetime. in the format "dd-mm-yyyy"

I tried to follow

Request:

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

Output:

01/11/2011

Query

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

Exit

2011-01-11 00:00:00.000

Request:

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

Exit:

11-1-2011 i.e. "dm-yyyy"

I need an output in the format "dd-mm-yyyy".

Answer 1

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

Answer 2

Try:

 SELECT convert(varchar, getdate(), 105) 

More details here .

Answer 3

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

Answer 4

Using the CONVERT function "works", but only if you are comparing strings with strings. To effectively compare dates, you really need to keep the SMALLDATETIME data type strictly typed on both sides of the equation (ie, "="). Therefore, the above “apros” comment is actually the best answer here because the blog mentioned has the correct formulas to use to cancel the time component, “smoothing” it until midnight (for example, 12:00:00) by rounding and any column dates in SQL Server 2005 will always be the default until 12:00:00 if the date is given without any time.

It worked for me ...

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