Finding values containing milli seconds!

Hi all...

I've got a group of tables and I wanted to first see which tables
contained a fields containing the "Date/Time" data type. And I was
successful in doing so...here's the query:


(
select a.name tablename, b.name colname
from sysobjects a, syscolumns b
where a.name = object_name(b.id)
AND B.TYPE = 61 AND A.XTYPE = 'U'

)


Now...my only issue is find which query I can run (On each table)
which returns to me ONLY the values containing the date/time stamp
with milli seconds (Ex: 01/01/2007 10:10:50:987)

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".

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.

Re: Finding values containing milli seconds!

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx