SQL date conversion changes time

I have a strange problem. I convert a date using the following

CONVERT(varchar(64), table.[Date], 120) AS the_date

Now, the strange thig is that if I don't run the conversion the time is for example 2011-01-10 13:10:01 abu开发者_StackOverflow中文版t if I run the conversion the time will become 2011-01-10 13:10:02. How can this be? I only run a conversion.

Update: The column is a datetime column. I have tried converting using 121 but same problem. If I list the unconverted value with the converted one side by side in SQL Server Enterprise Manager I get the difference on a row here and there. It seems Enterprise Manager also converts it when outputing it for viewing since if I fetch the data using PHP and don't convert I see that the column is saved like Jan 10 2011 3:31PM

Update: Running query analyzer I get

2011-01-05 10:49:48.557    2011-01-05 10:49:48

So that seems to have been the origin of the error. Haven't seen any errors while quickly looking through the result set now. Why can't the make aome software that works for once? :)

This query, run through query analyzer, or SSMS, returns the expected values:

select CONVERT(varchar(64),CONVERT(datetime,'2010-01-01T10:59:31.997'),120),CONVERT(datetime,'2010-01-01T10:59:31.997')

2010-01-01 10:59:31 2010-01-01 10:59:31.997

Running the same through Enterprise Manager yields:

2010-01-01 10:59:31 01/01/2010 10:59:32

That is, enterprise manager already applies formatting to datetime columns, rounds the output and hides milliseconds. It should not be trusted for, well, anything.

Rounding. There is probably a millisecond value there too.

Is the convert() rounding up the time?

There is info on rounding here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Show your problem using

select table.[Date], CONVERT(varchar(64), table.[Date], 120) AS the_date from table

and it could be a conversion problem

Use CONVERT(varchar(64), [Date], 121) to preserve the milliseconds.