Reputation: 103

SQL Date format

How I can convert 1/6/2011 12:00:00 AM to 01/06/2011 in MS SQL?

Upvotes: 0

Answers (3)

Reputation: 2817

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);

Upvotes: 0

Reputation: 8508

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

Upvotes: 1

Reputation: 27214

Upvotes: 2