Adding times past midnight

[Guest]

Our office is open from 7:45:00 am to 1:15:am the next day. My table has
dates and time every 1/2 hour.

Date Time Sign on Time
9/3/2005 3:30:00 PM 22.5
9/3/2005 4:00:00 PM 30
9/3/2005 4:30:00 PM 10
9/3/2005 5:00:00 PM 30
9/3/2005 5:30:00 PM 30
9/3/2005 6:00:00 PM 30
9/3/2005 6:30:00 PM 30
9/3/2005 7:00:00 PM 30
9/3/2005 7:30:00 PM 30
9/3/2005 8:00:00 PM 30
9/3/2005 8:30:00 PM 30
9/3/2005 9:00:00 PM 22
9/3/2005 9:30:00 PM 30
9/3/2005 10:00:00 PM 30
9/3/2005 10:30:00 PM 30
9/3/2005 11:00:00 PM 30
9/3/2005 11:30:00 PM 30
9/4/2005 12:00:00 AM 10.5

I want to make a query that add the Sign on Time from 7:45 am to 3:00 am the
next day. Can anyone help?

Thanks in advance.

Mike

 

[Tom Ellison]

Dear Mike:

I'm thinking you could use the DateDiff function to subtract 3 hours.
Everything up to 3 AM would then appear under the previous day's date.

It's not clear to me just what you want to do with this. But you could
group or aggregate by the DATE() of the DateDiff -3 hours.

Tom Ellison

 

[Marshall Barton]

Tom Ellison wrote:


Hellllooo Tom, haven't heard from you in a loooong time.

How's it going?

 

[Guest]

Dear Mike:

I'm thinking you could use the DateDiff function to subtract 3 hours.
Everything up to 3 AM would then appear under the previous day's date.

It's not clear to me just what you want to do with this. But you could
group or aggregate by the DATE() of the DateDiff -3 hours.

Tom Ellison

Thanks so much Tom...

 

[Tom Ellison]

Hey Marsh,

Sorry, I didn't see this sooner. Going better. Not an MVP any more. No
surprise. I didn't have enough sick time accumulated, I guess.

Great to hear from you. Betsy says hello, too!

Tom

 

[Marshall Barton]

Hey Marsh,

Sorry, I didn't see this sooner. Going better. Not an MVP any more. No
surprise. I didn't have enough sick time accumulated, I guess.

Great to hear from you. Betsy says hello, too!

Good to hear that things are picking up for you.

I've missed my periodic lesson in the use of
natural keys ;-)

Best of everything to you and Betsy