try this.
sql server 2000

/* Creating a Test Table */
Create Table MyDateTest99
(
DateColumn smalldatetime
)
go

/* Inserting the test value into the table */
/*insert into MyDateTest99 values (’2005-09-15 00:00:00′) */
insert into MyDateTest99 select convert (varchar(10),’2005-09-15 01:03:22′,120)

go

/* Selecting the result */
select DateColumn from MyDateTest99
go

/* Performing Cleanup */
drop table MyDateTest99
go

At first I went for the same solution as first mentioned by servee, but for some reason, my SQL Server did not return the needed.

I doubt, that the solution by chicotellez would work, unless your database runs certain languages, that by default removes the hh.mm from the smalldatetime.
- hh.mm is normally part of the smalldatetime!
Besides – creating an entire new table just to solve this problem seems a bit like shooting birds with canons!

Since the solution by servee didn’t work for me, I worked out this solution in stead… Not an optimal solution at all – I admit to that – but it works:

declare @date as datetime, @strDate as varchar(10)
set @date = getdate()
set @strDate = cast(day(@date) as varchar) +’-'+ cast(month(@date) as varchar) +’-'+ cast(year(@date) as varchar)
select cast(@strDate as datetime)

Hope someone has a better solution! (?)

Good luck
Jacob

Use the convert function.

select convert(varchar(10), '2005-09-14 09:49:13.873', 101)