Datetime without time !

I'm using the following statement to generate random dates:

DATEADD(day, DATEDIFF(day, 1, GETDATE()) - 1 - FLOOR(RAND(CAST(NEWID() AS binary开发者_如何转开发(4))) * 365.25 * 90), 10)

But, I'm getting the following format:

1974-01-28 00:00:00.000

How can I get rid of time part 00:00:00.000 ?

If you are on SQL Server 2008 then use the date datatype instead of datetime otherwise you can't. The datetime datatype always has a time component stored with it.

You can remove it for display purposes though by casting to a character datatype and passing a style argument. e.g.

select CONVERT(varchar,GETDATE(),105)

Returns

30-12-2010

Quite a comprehensive cheat sheet to these formats is here

You can use the DATE data type instead of the DATETIME datatime if you are using SQL Server 2008.

Or, you can use the CONVERT function to convert the date to a string-type (VARCHAR, CHAR, etc.) data type.

For example

CONVERT(VARCHAR, GETDATE(), 101)

produces this

12/29/2010

More on CONVERT formats

You can't keeping it as Datetime, unless you have SQL Server 2008 and use the Date datatype