DateDiff formatted results

  • Thread starter Thread starter Jeremy
  • Start date Start date
J

Jeremy

I have the following query. I used the hour method for the
DateDiff, but then I don't get the partial hours in a
decimal format. I switch to minutes/60, and I get 8
decimal places. Is there a way to format either so I only
show two decimal places? Thanks!

SELECT UT.Date, UT.[CSR Name], UT.[Leave time], UT.
[Scheduled Leave Time], DateDiff("n",UT.[Leave time],UT.
[Scheduled Leave Time])/60 AS Time_Saved
FROM UT
GROUP BY UT.Date, UT.[CSR Name], UT.[Leave time], UT.
[Scheduled Leave Time]
HAVING (((UT.Date)=Date()));
 
I have the following query. I used the hour method for the
DateDiff, but then I don't get the partial hours in a
decimal format. I switch to minutes/60, and I get 8
decimal places. Is there a way to format either so I only
show two decimal places? Thanks!
Click to expand...

Just round the result: for two decimals, try

SELECT UT.[Date], UT.[CSR Name], UT.[Leave time], UT.
[Scheduled Leave Time], Round(DateDiff("n",UT.[Leave time],UT.
[Scheduled Leave Time])/60, 2) AS Time_Saved
FROM UT
GROUP BY UT.[CSR Name], UT.[Leave time], UT.
[Scheduled Leave Time]
WHERE (((UT.[Date])=Date()));

Note that I've put square brackets around the reserved word Date; you
should probably change the fieldname since Access WILL get confused.
Also I changed the HAVING clause to WHERE and removed [Date] from the
group by - as written, the calculation will be done for the entire
table, all dates, and *then* limited to the results for today's date.
 
Back
Top