Time Clock Report - Query Help 1

[johnhig]

Good Morning -

I am having a problem with my query. I have a little timeclock table. I just want to output my data to a time clock report which display all time clock activity from yesterday.

So I created my query like the following:

SELECT Username, TimeIn, TimeOut, Status, DATEDIFF(hh, TimeIn, TimeOut) AS TotalHours, DATEDIFF(n, TimeIn, TimeOut) AS Totalminutes
FROM Clock2
WHERE timein = #CreateODBCDate(DateAdd("d", -1, Now()))#
GROUP BY ID, Username, TimeIn, TimeOut, Status
ORDER BY Username


When I generate my report. No data appears.

If I do change my WHERE clause to <= of course I get all prior data...where I just want yesterday.

What am I doing wrong?

Thanks John

 

[cfStarlight]

Your query must consider the time as well as the date

WHERE     timein >= #CreateODBCDate(DateAdd("d", -1, Now()))#
AND       timein < #CreateODBCDate(Now())#

 

[johnhig]

Oh okay. I understand now. Thanks for the help.

 

[cfStarlight]

Welcome

 

[johnhig]

One more question....Is there a reason why my minutes would be off?

When I run the query through query analyzer it pulls the correct data.

Then I run the .cfm page and it calls a template from the Report Builder (same query runs and on the .cfm page)

The Report Builder delivers the report and the hours are correct but the minutes are off.

For Example the sql table will say 7:06, but the report is display 7:10.

The time is correct on the servers. Not sure what I am doing wrong.

John

 

[johnhig]

Got it. It was all in my formatting.

I ended up using the LSTimeFormat and it works perfectly.