?
Solved

wrong results due to date-format?

Posted on 2008-11-07
14
Medium Priority
?
273 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:Kongta
  • 4
  • 4
  • 3
  • +1
14 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22903806
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22903813
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
 
LVL 16

Expert Comment

by:brad2575
ID: 22903822
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 5

Expert Comment

by:jfmador
ID: 22903845
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
 
LVL 5

Expert Comment

by:jfmador
ID: 22903890
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
 

Author Comment

by:Kongta
ID: 22903913
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
 
LVL 16

Accepted Solution

by:
brad2575 earned 2000 total points
ID: 22903942
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
 
LVL 5

Expert Comment

by:jfmador
ID: 22903947
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
 

Author Comment

by:Kongta
ID: 22903990
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
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22904181
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
 
LVL 16

Expert Comment

by:brad2575
ID: 22904186
should be a lot faster as you do not need to do all the converts.

0
 

Author Comment

by:Kongta
ID: 22904380
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
 
LVL 16

Expert Comment

by:brad2575
ID: 22904770
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
 

Author Comment

by:Kongta
ID: 22904851
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question