Posted by Erland Sommarskog on 05/23/07 22:10

Mohd Al Junaibi (mohamed.aljunaibi@gmail.com) writes:
> I require this since we are planning on shifting those tables to the
> Oracle platform, and we've had issues doing this while loading the
> extracted information, since it was not taking the values containing
> the "milliseconds".

SELECT datetimecol
FROM tbl
WHERE datepart(ms, datetimecol) <> 0

> Is there a way to do this? Or is it better to simply remove the
> milliseconds all togeather? I wouldn't mind doing so provided that it
> doesn't includes changes to the actual data type.

UPDATE tbl
SET datetimecol = dateadd(ms, -datepart(ms, datetimecol), datetimecol)
WHERE datepart(ms, datetimecol) <> 0

Caveat: I did not test this.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Synchronous Bulk-Copy into two table
• Prev in forum: Re: Question about Transaction isolation level="readCommitted"
• Thread view: Re: Finding values containing milli seconds!

[Reply to this message]