sql server 2000
/* Creating a Test Table */
Create Table MyDateTest99
/* 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)
/* Selecting the result */
select DateColumn from MyDateTest99
/* Performing Cleanup */
drop table MyDateTest99
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! (?)
Use the convert function.
select convert(varchar(10), '2005-09-14 09:49:13.873', 101)