problem comparing datetime fields

[Glowworm27]

Hello all,

I have an ssis package that retrieves records from a table based on a datetime field. I also have a table that keeps track of the max(datetime) stamp of the records so that the next time my code is run it gets the next set of records.

However what seems to be happening is the comparison code is not checking down to the millisecond and I am getting duplicate records on the following run.? Like the last record from day one, is the first record to be sent on day two.

To retrieve the records I do something like this...

First I get the last datetime stamp into a ssis datetime variable using the follwing SQL, and set the resultset to singlerow, and store the result in the "lastDatetime" variable

(select max(DateCreated) as maxdate from historytable where process = 'daily export' )

I then retrieve the records from the main table where the Create_date is greater than the variable "lastDateTime"

Insert into temptable
Select * from maintable where Create_date > ?

where the ? parameter is set to the package variable "lastdateTime"

Then I will export these records into a flat file and they get ftp'd somewhere. After these records get exported I then update the historytable with the following

Update historytable set DateCreated = (Select Max(Create_date) from temptable)
where process = 'daily export'

my question is when the records are compared to the package variable, or during the Max(Create_date) function does SQL compare this down to the millisecond?????

thanks for any insight you may be able to provide

G

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[MDXer]

what is the data type in the package of the variable lastdateTime.

 

[Glowworm27]

everything is set to datetime data types. there is no conversion of any datetime fields


George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[Glowworm27]

OK , so far what I have been reading is that SSIS variable datetime data types will truncate milliseconds.

So to try to solve my problem, I use a direct query into the historytable, rather than save the result in a package variable.

Then I still retrieve the records into the temp table, and to update the historytable, I will again do the Select Max(create_date) from the historytable

our auditor is going thru the data now to see if this has resolved the problem.

the best thing to work around this if you need to store the full datetime stamp in a package variable is to convert the datetime stamp into a string, and use a string package variable.

select convert(varchar(23), max(DateCreated), 121) as DateCreated
from historytable where process = 'daily export'

then store the results of this query into a varchar data type variable in the SSIS package.


George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net