Confusion using Convert to return Date only... 1

[jsteph]

I'm trying the below:
select convert(datetime,getdate(),101)

Yet this returns the time portion, even though the 101 parameter specifically says it formats the conversion in mm/dd/yy.

I would expect:
select convert(datetime,'5/5/1955 5:55 AM'101)
...should return:
[COLOR=blue ]5/5/1955[/color]
...but instead it returns:
[COLOR=blue ]2005-05-05 05:55:00.000[/color]
I would even accept:
[COLOR=blue ]2005-05-05 00:00:00.000[/color]
...because this will be used in a sql statement against a datetime field, but the stored data has no time component, ie, it's midnight.

So what is the *style* parameter for if it doesn't seem to do anything close to what it states?

What I'm really looking to do is find the shortest way to strip the time from a datetime value--but keep it a datetime data type--again--force the time to be midnight.

I tried using Varchar in the convert(), and it seemed to work--but again--why would I want to convert to varchar--it's a Datetime that I want. If that's "the way" then fine, but I just figured I'd convert a datetime to a datetime with an empty or zero time.
Thanks
--Jim

 

[sqlcasey]

You can try this:

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

or this:

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))

To truncate off the time part, I think you have to convert the datetime to a varchar - someone correct me if I'm wrong.

-Casey

 

[gmmastros]

sqlcasey is right. If you want to use convert to do this, you must first convert to varchar, and then to DateTime. Casey's method involves math caculations which would be faster than string conversion.

The convert method (not recommended):

Declare @TestDate DateTime
Set @TestDate = '5/5/1955 5:55 AM'

select Convert(DateTime, convert(varchar(10), @TestDate,101))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jsteph]

Thanks for that, I'll do the dateadd/datediff one, since it's faster.
--Jim