Using Between with Time

[Vassago]

Hello,

I have a query I need to run that will check a table with a datetime field with various dates and times. I need it to make a field a 1 if the time shown regardless of the date falls between 8:00:00 am and 12:00:00 pm. I need another field to show as 1 If it the time is between 12:00:01pm and 5:00:00pm and another one between 5:00:01pm and 11:00:00pm. I tried the following query, but the results are not calculating correctly. Can someone give me a hand?

SELECT tbl_termcodes.DLListName, tbl_termcodes.DLList, tbl_termcodes.Account, IIf(Format([Calltime],"hh:nn:ss") Between #12/30/1899 8:0:0# And #12/30/1899 12:0:0#,1,0) AS [8-12], IIf(Format([Calltime],"hh:nn:ss") Between #12/30/1899 12:0:0# And #12/30/1899 17:0:0#,1,0) AS [12-5], IIf(Format([Calltime],"hh:nn:ss") Between #12/30/1899 17:0:0# And #12/30/1899 23:0:0#,1,0) AS [5-11]
FROM tbl_termcodes;

Thanks!

Vassago

 

[pdx_man]

Just test the fractional portion of the date:

SELECT tbl_termcodes.CallTime, IIf(CDbl([calltime])-Int([calltime])>=0.33333 And CDbl([calltime])-Int([calltime])<=0.5,1,0) AS 8_to_Noon, IIf(CDbl([calltime])-Int([calltime])>0.5 And CDbl([calltime])-Int([calltime])<0.70834,1,0) AS Noon_to_5, IIf(CDbl([calltime])-Int([calltime])>0.70834 And CDbl([calltime])-Int([calltime])<0.95834,1,0) AS 5_to_Eleven
FROM tbl_termcodes;

 

[Vassago]

Just test the fractional portion of the date:

SELECT tbl_termcodes.CallTime, IIf(CDbl([calltime])-Int([calltime])>=0.33333 And CDbl([calltime])-Int([calltime])<=0.5,1,0) AS 8_to_Noon, IIf(CDbl([calltime])-Int([calltime])>0.5 And CDbl([calltime])-Int([calltime])<0.70834,1,0) AS Noon_to_5, IIf(CDbl([calltime])-Int([calltime])>0.70834 And CDbl([calltime])-Int([calltime])<0.95834,1,0) AS 5_to_Eleven
FROM tbl_termcodes;

You are a lifesaver! Awesome! Thank you very much for your help!

Out of curiousity, how do you calculate what the fraction is for each time?

 

[pdx_man]

That is how it is actually stored.

08/04/2006 9:56:00 AM = 38933.4138889

Access takes the 38933.4138889 and displays it as 08/04/2006 9:56:00 AM

The .4138889 is the fraction of 24 hours the time represents, or 9:56:00 AM
add .5 to it and you will see 12 hours added to your time.

.5 = 12 hours, half a day.

So, taking CDbl(CallTime) will return the numeric equivalent.
(You can also just multiply it by 1, but I prefer to explicitly convert)
Int(CallTime) drops the fractional portion, so then:
CDbl(CallTime) - Int(CallTime) leaves only the fractional part.
Check it out.

 

[Vassago]

That is how it is actually stored.

08/04/2006 9:56:00 AM = 38933.4138889

Access takes the 38933.4138889 and displays it as 08/04/2006 9:56:00 AM

The .4138889 is the fraction of 24 hours the time represents, or 9:56:00 AM
add .5 to it and you will see 12 hours added to your time.

.5 = 12 hours, half a day.

So, taking CDbl(CallTime) will return the numeric equivalent.
(You can also just multiply it by 1, but I prefer to explicitly convert)
Int(CallTime) drops the fractional portion, so then:
CDbl(CallTime) - Int(CallTime) leaves only the fractional part.
Check it out.

Ahhhh! Understood. Very clever.

 

[EMP]

Alternatively, you can use the TimeValue() function.

SELECT [Calltime],
IIf(TimeValue([Calltime]) Between #12/30/1899 8:0:0# And #12/30/1899 12:0:0#,1,0) AS [8-12],
IIf(TimeValue([Calltime]) Between #12/30/1899 12:0:1# And #12/30/1899 17:0:0#,1,0) AS [12-5],
IIf(TimeValue([Calltime]) Between #12/30/1899 17:0:1# And #12/30/1899 23:0:0#,1,0) AS [5-11]
FROM tbl_termcodes;

^