I'm losing fidelity in a computed column and I'm not sure what is causing it. I have two datetime columns and my computed column is the difference between them in hours.
When I change the 'outTime' value minute by minute, the computed value changes every 5 or 6 minutes i.e. .90625, .90625, .90625, .90625 .90625, .8125, .8125......
Should I make the conversion as a decimal instead?
JonnyBravoII
Member
43 Points
74 Posts
Computing difference between two dates
May 05, 2012 03:32 PM|LINK
I'm losing fidelity in a computed column and I'm not sure what is causing it. I have two datetime columns and my computed column is the difference between them in hours.
((CONVERT([real],[outTime],(2))-CONVERT([real],[inTime],(2)))*(24))
When I change the 'outTime' value minute by minute, the computed value changes every 5 or 6 minutes i.e. .90625, .90625, .90625, .90625 .90625, .8125, .8125......
Should I make the conversion as a decimal instead?
limno
All-Star
117314 Points
7997 Posts
Moderator
MVP
Re: Computing difference between two dates
May 05, 2012 04:05 PM|LINK
You can use the builtin datediff function to return an integer for example:
ALTER TABLE yourtable ADD myDiff AS (datediff(minute,[outtime],[intime])) PERSISTED
You can change the unit from minute to second or day at your choice.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
JonnyBravoII
Member
43 Points
74 Posts
Re: Computing difference between two dates
May 05, 2012 07:34 PM|LINK
Worked like a champ. Thanks much.