September 16, 2008 at 12:09 am
Dear Gurus,
This is the first time encountering such problem with Between 'StartDate' and 'EndDate'
Well i have 2 Tables to compare say ...
'tblA' = oracle DB
'tblB' = SQL2000 DB
'tblA' returns 65 rows record count on code below
select distinct count (patient_id) from oratbl where (trunc(RECEIVED_DATE) between '01-JAN-2008' and '31-JAN-2008')
but
'tblB' returns only 63
SELECT DISTINCT COUNT(PATIENT_ID) AS Expr1
FROM sql2000tbl
WHERE (RECEIVED_DATE BETWEEN '01-JAN-2008' AND '31-JAN-2008')
Well 'tblB' is a replicate from 'tblA' where 'tblB' was created using ODI for data warehousing purposes.
First, thought that it would be due to ODI loading probs from ora to sql2000. but nope.. coz both tblA and tblB has the same amount of recordset of 660 records.
Just dun get it why sql2000 query which is the same to ora's query both have the same amount of total recordset of 660.. and it just doesn't show the same amount of records filtered for month JAN.
Again verified from sql2000 db the missing records within the JAN date filter those 2 missing records has received_date of 31/1/08 ... Would this be some settings on sql2000 that i need to checked on?
Really appreciate if anyone would be able to point some pointers.. as of this is my first time encountering such scenario where between date function not returning full recordset of provided between date1 and date2 sql200 function..
Regards,
Terri
September 16, 2008 at 1:21 am
Have a look at the date on those 2 missing records... I suspect there will be a time portion to the date.
In SQL Server, if you don't specify the time as well as the date, it defaults to midnight, so you will be missing all the records with a date beyond midnight on 31st January.
Change the query to
BETWEEN '01-JAN-2008' AND '31-JAN-2008 23:59:59.997'
or
RECEIVED_DATE >= '01-JAN-2008' AND RECEIVED_DATE < '1 FEB-2008'
September 16, 2008 at 8:27 pm
Hey Ian,
thanks for the reply..
i can't test it till tommorow coz my i am off from work today..
Well just would like to ask.. the thing is i'm working on a cube for 'TBLB' = FactTable in Analysis Manager
but when processed on period year month date drill down..
31st data's are missing.. i reckon it could be the datetime stamp which is tickling this matter.
Would this be anything to do with table properties in sql2000? like example compatibility settings or any settings that needs to be checked to prevent such to happen?
Coz able to query this manually doesn't mean that analysis manager could.. hmm what are your suggestions?
Gladly appreciated,
Terri
September 17, 2008 at 1:20 am
Terri .K Wei (9/16/2008)
Would this be anything to do with table properties in sql2000? like example compatibility settings or any settings that needs to be checked to prevent such to happen?
No. It depends how you're pulling them in from Oracle. In SQL, datetimes always have time portions. What you may want to do is look a the import process and see if you can set the time to midnight during the load (assuming the time portion of the date doesn't have significance elsewhere)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply