DateDiff

1. Senior Member
Join Date
Dec 1969
Posts
688

## DateDiff

I have a table with 2 datetime fields plus a number of days field.(decimal - accurate to 2 decimal places)<BR><BR>I am testing a stored procedure in query analyser which calculates the number of days between the 2 dates:<BR><BR>update tblpolicies<BR>set PolicyDays = datediff("d", PolicyStartDate,PolicyModified)<BR><BR>This is returning a rounded up / down result whereas I want it to return a decimal such as 9.25 days<BR><BR>I have also tried this:<BR><BR>update tblpolicies<BR>set PolicyDays = datediff("hh", PolicyStartDate,PolicyModified)/24<BR><BR>It still rounds it.<BR><BR>The only way I have got it to work is using 2 updates:<BR><BR>update tblpolicies<BR>set PolicyDays = datediff("hh", PolicyStartDate,PolicyModified)<BR>update tblpolicies<BR>set PolicyDays = policydays/24<BR><BR>I would like to get the decimal result with just one update - any ideas?

2. Senior Member
Join Date
Dec 1969
Posts
415

## Close

The return type of datediff is an integer so when you do math on an integer type you always end up with an integer result. To get your decimal answer you have to change the type before you do the division.<BR><BR>Try:<BR><BR>Cast(datediff("hh",Po licyStartDate,PolicyModified) as decimal)/24<BR>

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## That will *NOT* be accurate...

...to two decimal places, though.<BR><BR>I would use minutes, instead, and divide by 1440 (24 * 60). That will give two digits plus of accuracy.<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
688