wrong results due to date-format?

I do have the tbl dbo.GFArchiv where I have millions of rst including dbo.GFArchiv.DatumBerechnung in datetime format and a tbl dbo.GF_V1_Investmentratio with Datum, formerly in smalldatetime changed into datetime but did not solve issue. I previously deleted all entries in dbo.GF_V1_Investmentratio  and feeded all new with  dbo.GFArchiv.DatumBerechnung.Datum which worked fine but took more then an hour to execute. Therefore, I would like to insert only the latest dates of  dbo.GFArchiv.DatumBerechnung  which are not yet included in dbo.GF_V1_Investmentratio to perform faster. Trying

SELECT DISTINCT dbo.GFArchiv.DatumBerechnung
FROM         dbo.GFArchiv LEFT OUTER JOIN
                      dbo.GF_V1_Investmentratio ON CONVERT(smalldatetime, CONVERT(char(10), dbo.GFArchiv.DatumBerechnung, 104), 104) > CONVERT(smalldatetime,
                      CONVERT(char(10), dbo.GF_V1_Investmentratio.Datum, 104), 104)

brings back all dates of dbo.GFArchiv.DatumBerechnung and not only > dbo.GF_V1_Investmentratio.Datum

any idea where the bug is?
KongtaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
SELECT DISTINCT dbo.GFArchiv.DatumBerechnung
FROM    dbo.GFArchiv
WHERE DateAdd(dd, 0, DateDiff(dd, 0, dbo.GFArchiv.DatumBerechnung) > 
(SELECT MAX(DateAdd(dd, 0, DateDiff(dd, 0, dbo.GF_V1_Investmentratio.Datum))) FROM dbo.GF_V1_Investmentratio)
0
Kevin CrossChief Technology OfficerCommented:
If I am understanding you correctly, the above query I suggested will get you dates that are greater than the max date in the other table.
0
brad2575Commented:
the way your query is set up wont work.

So you have data in the table and only want to insert data that has not been inserted yet by che3cking the date?  

Will this logic work:  Can you just get the highest date that exists in the table already and only get data to insert that are greater than that date?

If so you can do something like this:

Select DataFieldsToInsert
From dbo.GF_V1_Investmentratio
Where dbo.GF_V1_Investmentratio.Datum >=
    (
       Select MAX(dbo.GFArchiv.DatumBerechnung) FROM dbo.GFArchiv
    )
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jfmadorCommented:
Hello Kongta, your query will join on your two table where the date in archiv is bigger than the date in investmentratio, it will bring you all the archiv where the date is after any date in investment ratio.

You should at least use a max date on investment ratio

SELECT *
FROM  dbo.GFArchiv 
WHERE (smalldatetime, CONVERT(char(10), dbo.GFArchiv.DatumBerechnung, 104), 104) > (SELECT MAX(CONVERT(smalldatetime,                       CONVERT(char(10), bo.GF_V1_Investmentratio.Datum, 104), 104))
FROM dbo.GF_V1_Investmentratio)

Open in new window

0
jfmadorCommented:
hooo i'm too late

Also if the date that you use in your table are not ordered, for exemple an invoice date, you can receive an invoice from a supplier later than another one but with a smallest date, then you could not use this date to retrieve your data because you could miss the one that are add to your system with a smaller date than your last import.

In this case I would suggest you to add a status field on your first table, Transfered as a int where the value could stand as 0 - Not Transfered, 1 - Transfert in process, 2 - Transfered.

Before importing your data you can do an update on your table to change all the 0 to 1, then Only import the 1, after that update the 1 as 2. It will prevent you missing data that people add to your system while transfering.
0
KongtaAuthor Commented:
tried all three but always got an error in the QueryAnalyzer,  anyhow seems that I haven't described for a clear understanding as english is not my motherlanguage. In the table GFArchiv, I do have dates back to 2002, where in GF_V1_Investmentratio I would like to add only the dates that are not open yet like

GFArchiv.DatumBerechnung                     GF_V1_Investmentratio.Datum
01.11.2008                                                 01.11.2008
02.11.2008                                                 02.11.2008
3.11.2008
4.11.2008
5.11.2008


in this case, I would like to add 3.11.-5.11.2008 into the GF_V1_Investmentratio table
0
brad2575Commented:
the above queries should work for what you describe above then except reverse the table names.  So try this:

Select GFArchiv.DatumBerechnung
From GFArchiv
Where GFArchiv.DatumBerechnung  >=
    (
       Select MAX(GF_V1_Investmentratio.Datum) FROM GF_V1_Investmentratio
    )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfmadorCommented:
Sorry I forgot a Convert in mine, what is the error you got ?

SELECT *
FROM  dbo.GFArchiv
WHERE convert(smalldatetime, CONVERT(char(10), dbo.GFArchiv.DatumBerechnung, 104), 104) > (SELECT MAX(CONVERT(smalldatetime,                       CONVERT(char(10), bo.GF_V1_Investmentratio.Datum, 104), 104))
FROM dbo.GF_V1_Investmentratio)
0
KongtaAuthor Commented:
brad2575, just run your code but takes already few minutes, don't know yet what the outcome is, hope doesn't take 45' which would be no improvement in time
0
Kevin CrossChief Technology OfficerCommented:
From your description this may just be simply this:
SELECT DatumBerechnung 
FROM GFArchiv
WHERE DatumBerechnung NOT IN (SELECT Datum FROM GF_V1_Investmentratio)

Open in new window

0
brad2575Commented:
should be a lot faster as you do not need to do all the converts.

0
KongtaAuthor Commented:
ok, took me now 50', not faster at all. As GFArchiv has about 20'000 rst on each day, 01.11.2008 comes back 20'000 for example, I changed now into SELECT DISTINCT and hope for a faster query, just start run and wait
0
brad2575Commented:
do you have an auto increment ID field in the GFArchiv.DatumBerechnung  table?

If so what you can do is save last ID you inserted into a lookup/temp table and use that in your query for the next day.

So say yesterday you inserted from the GFArchiv.DatumBerechnung  table and the last ID you inserted was 1001.  You save that ID in a temp/lookup table.  Then tomorrow when you go to do the insert again you just select all the data from the GFArchiv.DatumBerechnung  table that have an ID greater then 1001 (from the table) - say 100 new records (Id's from 1101 - 1201).  Then update that number in the temp/lookup table to 1201 (as this was the last inserted ID for today).

This would save having to do any date checks at all and would speed things up tremendously.

So if you did this your query would be something like this:
Select GFArchiv.DatumBerechnung
From GFArchiv
Where GFArchiv.AutoIncrementID  >
(
       Select LastInsertedID From TempLookupTable
    )
0
KongtaAuthor Commented:
ok brad, good input. let me have a try, anyhow as the initial problem has been solved, I will award you the points, I hope everybody here is ok with that, thx to all and have a nice weekend
rgds
Kongta
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.