Finding values containing milli seconds!

[Mohd Al Junaibi]

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.i d)
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 "millisecon ds".

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.

[Erland Sommarskog]

Mohd Al Junaibi (mo************ ***@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 "millisecon ds".

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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx