March 30, 2009 at 4:07 am
Hi Masters,
I receive data from a txt file and store this data on a table. The field date (that i receive from this txt) is rounded, i don't know why.
It does not show the miliseconds.
I need to compare this date, with the date that i have on other columns, but i don't know how to do it.
For instance:
this two dates are the same! but one is rouded (received from the txt) and the other is not:
1) 2007-11-09 13:23:25.000
2) 2007-11-09 13:23:24.973
I need that this comparation :
select * from table1, table2 where table1.date>table2.date
in this case does not show any results, because the two dates are the same, but one is rounded.
Can you please help?
tks,
Pedro
March 30, 2009 at 4:36 am
You didn’t specify how the rounding was done, so I’m assuming that it was using 500 ms as the “border” (bellow 500 ms rounded to the same second, 500 ms or above rounded to the next second). If this is the case you can use datediff function to check the difference between the 2 dates. You can use the results as the input of abs function (absolute value) and check if the difference between the 2 dates is less or more then 500 ms. If it is less then 500, then it is the same date. If it is more, then those are different dates.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 30, 2009 at 4:41 am
hi adi,
Tks for your reply.
But how can i do that inside this condition?
a.dta_alteracao>b.dta_alteracao
tks,
Pedro
March 30, 2009 at 4:50 am
Just add the following to your where clause:
and abs(datediff(ms, b.dta_alteracao, a.dta_alteracao)) > 500
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 30, 2009 at 5:14 am
I just put what you told ADi, but i receive the folowing error, when running the query:
"Difference of two datetime columns caused overflow at runtime"
what can cause this situation?
tks,
Pedro
March 30, 2009 at 6:07 am
You get the error because the value between your two date is bigger than the maximum value allow for an integer.
Try this instead
declare @dt1 datetime,
@dt2 datetime
set @dt1 = '2007-11-09 13:23:25.000'
set @dt2 = '2007-11-09 13:23:24.973'
SELECT @dt1,
CASE WHEN DATEPART(ms,@dt2) < 500
THEN DATEADD(ms,-DATEPART(ms,@dt2),@dt2)
ELSE DATEADD(ms,1000 - DATEPART(ms,@dt2),@dt2)
END
You can experimant with the above to confirm that it is behaving as you expect. You can then change your query thus:
SELECT *
FROM table1,table2
WHERE table1.date_rounded > CASE WHEN DATEPART(ms,date_not_rounded) < 500
THEN DATEADD(ms,-DATEPARTms,date_not_rounded),date_not_rounded)
ELSE DATEADD(ms,1000 - DATEPART(ms,date_not_rounded),date_not_rounded)
END
Mike
March 30, 2009 at 9:56 am
tks,
It worked very good....
Pedro
April 1, 2009 at 3:27 am
Just a thought. Why not round the non-rounded datetime and then do the comparison?
April 1, 2009 at 3:45 am
because i would have to make the APP that inserts the dates into the fields and i can not do that, because it's not my app.
tks,
Pedro
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply