loosing seconds of datetime datatype.... 1

[ruupy]

Hello u all, i've been reading some 'datetime'related entries but my problem remains unsolved.

I've got a table with a datetime-column (hhhh-mm-dd hh:mm:ss.mmm) and about 1000000 records.

I need to devide the table in several tables with the records of 1 month.
When i copy the data of 1 month to a second table, using;
***********************************************
insert into [surfdata2]
select * from surfdata
where datepart(month, logtime) = 2
order by logtime
***********************************************

...i loose the seconds of my logtimes!

e.g.

2001-02-01 20:14:23.000 (as present in surfdata)
turns into
2001-02-01 20:14:00.000

I'm at a total loss here, it's only an 'insert into' based on a 'select' statement?!.....

What am i missing here?

 

[ColinM]

Perhaps the second table is a smalldatetime field rather than a normal datetime?
SmallDateTime only stores to the nearest minute.

 

[rajeevnandanmishra]

Hi, ruupy

Instead of running INSERT INTO <table name> SELECT ....
try to run this:

select * INTO surfdata2 from surfdata
where datepart(month, logtime) = 2
order by logtime


May be it solve your problem.

 

[ruupy]

well,

I already checked the data type, the're both 'datetime' and running
********************************
select * INTO surfdata2 from surfdata
where datepart(month, logtime) = 2
order by logtime
********************************
Doesn't change anything...
I'm still loosing my seconds...

Am i doomed to copying the entire table and then deleting the records NOT needed??

.....RuupY

 

[foxdev]

Ruupy, reading through some of the MSDN reports, let me ask this:

what client are you using? IOW, are you doing this within a stored procedure? ADO/VB? Query Analyzer?

Some KB articles on MSDN are implying that either ODBC or the MS Jet engine may be altering the fractional seconds. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[ruupy]

Well FoxDev,

It's the Query analiser i'm using and it needn't be put into a SP, it's a 1 time operation.

The weird thing is that when i do a select with the same 'datepart'-statement in the 'where'-clause, the recordset DOES contain my valuable seconds!!!!
It's when i transfere them to an other table or when i delete a part of the table containing records that are not needed, that i loose my seconds.

RuupY