DateDiff not factoring in AM/PM?

I've attached some code and the data results.  What I think is happening is the code is not factoring in the am/pm.  Why else would it show 9 hrs as the dif between 12:00 am and 9:00 am?  What do I need to change to fix this?

SELECT     a.Name, a.EmployeeId, a.Expected, a.Actual, n.ShiftNote, u.UserName, CONVERT(varchar, FLOOR(a.Tardy / 60.0)) + ':' + RIGHT('0' + CONVERT(varchar, a.Tardy % 60), 
                      2) AS HrMin
FROM         (SELECT     CONVERT(varchar(20), s.TimeIn, 100) AS Expected, s.TimeIn, l.LastName, l.FirstName, l.EmployeeId, l.ManagerUID, 
                                              l.LastName + ', ' + l.FirstName AS Name, CONVERT(varchar(20), MIN(h.TimeIn), 100) AS Actual, CASE WHEN s.TimeIn < MIN(h.TimeIn) 
                                              THEN DATEDIFF(MINUTE, s.TimeIn, ISNULL(MIN(h.TimeIn), s.TimeIn)) END AS Tardy, MIN(h.RecordId) AS RecordId
                       FROM          dbo.EmployeeHours AS h INNER JOIN
                                              dbo.EmployeeSchedules AS s ON h.EmployeeId = s.EmployeeId AND DATEADD(day, 0, DATEDIFF(DAY, 0, s.TimeIn)) = DATEADD(DAY, 0, DATEDIFF(DAY, 0, 
                                              h.TimeIn)) INNER JOIN
                                              dbo.EmployeeList AS l ON s.Company = l.Company AND s.EmployeeId = l.EmployeeId
                       WHERE      (l.Suspend = 0) AND (DATEADD(DAY, 0, DATEDIFF(DAY, 0, h.TimeIn)) BETWEEN @From AND @To)
                       GROUP BY l.EmployeeId, l.ManagerUID, s.TimeIn, l.LastName, l.FirstName) AS a LEFT OUTER JOIN
                      dbo.UserList AS u ON a.ManagerUID = u.UID LEFT OUTER JOIN
                      dbo.EmployeeShiftNotes AS n ON a.RecordId = n.RecordId AND a.EmployeeId = n.EmployeeId
WHERE     (a.Tardy >= 0) AND (u.UserName = @Supervisor)
ORDER BY a.LastName, a.FirstName, a.TimeIn

Results...
Emp Id	Name	Expected	           Actual	       Hrs:Min
3227	Doe, Jane	Sep  2 2011 11:30AM	Sep  2 2011 11:31AM	0:01
		Sep  5 2011 12:00AM	Sep  5 2011  9:00AM	9:00
		Sep  8 2011  9:00AM	Sep  8 2011  9:01AM	0:01

Open in new window

BobRosasAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
BobRosas,

>>Why else would it show 9 hrs as the dif between 12:00 am and 9:00 am?

Why do you think that's the wrong answer?

I mean, if you go from 12:00 AM to 9:00 AM, that is 9 hours.

:)

Patrick
0
 
BobRosasAuthor Commented:
You are right.  I was reading the data and knew what it should say (12:00pm)  but didn't read what it actually said.  Sorry to bother you!
0
 
BobRosasAuthor Commented:
Thanks again!
0
 
Patrick MatthewsCommented:
We all have those days :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.