3

Default schedule for Steve Employee is Monday - Friday from 6:00 PM to 2:00 AM.

startdate = 10/14/2018 
starttime = 6:00 PM 
endtime = 2:00 AM (the next day)

When I generate the schedule in ColdFusion, it loops through each date where DayofWeek is in (2,3,4,5,6 which is Monday - Friday) but it doesn't know the end time is not the same date as the start time because it passed midnight during his shift.

How can I loop through the times to find the date when the time goes past midnight?

His shift is 8 hours long, but 

#DateDiff('n',starttime,endtime)#

is returning -16 hours,

so I can't even tell how many hours this time range is, or I could do a loop from the starting-date-time and add 8 hours.

This loop doesn't even run.

<cfset startTime = "#startdate# #starttime#"> 
<cfset endTime = #endtime#> 
<cfloop from="#startTime#" to="#endTime#" index="i" step="#CreateTimeSpan(0,1,0,0)#"> 
<cfoutput>#DateTimeFormat(i)#</cfoutput> 
</cfloop>

Desired result:

10/14/2018 6:00 PM
10/14/2018 7:00 PM
10/14/2018 8:00 PM
10/14/2018 9:00 PM
10/14/2018 10:00 PM
10/14/2018 11:00 PM
10/15/2018 12:00 AM
10/15/2018 1:00 AM

Thanks for your ideas.

Bobmd
  • 125
  • 7
  • 4
    Instead of end time, use shift hours, then you can calculate the shift end accurately, regardless of it crossing midnight – Rodrigo Murillo Oct 14 '18 at 22:11
  • 3
    You could I nitialize your start date/time and then use DateAdd() on that date/time as you iterate. – Redtopia Oct 14 '18 at 23:10
  • OK I will try that instead. Thanks! – Bobmd Oct 15 '18 at 00:17
  • 3
    Don't forget time values still have a default date (12/30/1899) even if it's not displayed. So `#DateDiff('n',starttime,endtime)#` translates to `dateDiff("h", "12/30/1899 18:00", "12/30/1899 02:00")` which equals -16 hours. The default date portion is also why the cfloop never executes. It's attempting to *increment* `from` a date (10/14/2018 6:00 PM) that's already greater than the `to` value (12/30/1899 02:00). – SOS Oct 15 '18 at 00:39
  • I got it to work using "hours" instead of "endtime". Thanks guys! yeah, that's the problem I was facing Ageax. I don't think there's a solution. ColdFusion doesn't know that it passed midnight if you just iterate through time values. – Bobmd Oct 15 '18 at 04:17
  • 1
    @Bobmd - Kind of .. conceptually, there is no "past midnight" with times. It's just hours 0-23. However, since CF times do include a date portion, you need to account for that and adjust the `endTime` to the correct *date*. Assuming work shifts are always < 24 hours, check whether `startTime gt endTime` and if yes, add 24 hours: `DateDiff('n',starttime,endtime)+24` == 8 hours. That said, the suggestions by RodrigoM and Redtopia are a better way to go IMO. – SOS Oct 15 '18 at 12:58
  • Just a note for future readers, your final loop doesn't run because the values you're passing to `from`, `to` and `step` are invalid. They take `numeric` argument but you passed in `date/time` objects. https://cfdocs.org/cfloop – Shawn Oct 15 '18 at 15:58
  • True, the values must be numeric, but date/time objects are numbers, so they should work fine. CF may also convert valid date *strings* into objects automatically, but... relying on implicit conversion isn't a great idea. I'd go with the other suggestions involving date objects and dateAdd() instead. – SOS Oct 16 '18 at 17:15

0 Answers0