SQL Date format

How开发者_JAVA百科 I can convert 1/6/2011 12:00:00 AM to 01/06/2011 in MS SQL?

---

Use Format 103.

---

If your aim is to compare two datetime based only on date, this should help

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

In Sql Server 2008 there is also the type DATE so a

CAST(@date as DATE)

should work too. For example this code

declare @dt as datetime
declare @d as date

set @dt = getdate()
set @d = cast(@dt as date)

print @dt
print @d

Has this output

mar 31 2011 11:46AM
2011-03-31

---

convert(char(10),aDate,101);

Does it work for your question?

Edit: if '1/6/2011 12:00:00 AM' is a string.

declare @aDate datetime;
set @aDate = '1/6/2011 12:00:00 AM';
print convert(char(10),@aDate,101);