| Author |
Message |
barnwpk
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
|
  
Topic: Date and Time
Posted: 14 Dec 2011 at 8:40am |
Need a report that captures all tickets that are created.... only between the hours of 5:30pm and 7:30 am each day for the past month.
date and time field is called TikALL.TicketDateTime.
Example of data in this field field looks like ..... 12/1/2011 10:56:49AM
Any help would be greatly appreciated.
Thank you. Edited by barnwpk - 14 Dec 2011 at 8:42am
|
 IP Logged |
|
|
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 14 Dec 2011 at 9:03am |
if you dont need compound select
datepart('m',{TicketDateTime})={parameter month}
and
timevalue({TicketDateTime}>=timeserial(17,30,0) and
timevalue({TicketDateTime}>=timeserial(7,30,0)
I am sure this can be shortened, this is all I could think of offhand
this may work
DateTime (CDate ({TicketDateTime}), CTime("5:30pm")) >={TicketDateTime} and
DateTime (CDate ({TicketDateTime}), CTime("7:30pm")) <={TicketDateTime} Edited by comatt1 - 14 Dec 2011 at 9:08am
|
| IP Logged |
|
barnwpk
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 14 Dec 2011 at 9:21am |
Thank you so much COMATT1. That works perfectly. I have been staring at this for so long. Thank you....Thank you.
|
| IP Logged |
|
barnwpk
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 15 Dec 2011 at 2:49am |
Another question....
This works great for all the days of the week.
timevalue({TikALL.TicketDateTime})>=timeserial(17,30,0) or
timevalue({TikALL.TicketDateTime})<=timeserial(7,30,0)
How do I get the above formula for M-F information, and on the weekend I need it to look at all time frames. The weekend needs to capture all information no matter what the time....and M-F only needs to capture information between 5:30pm and 7:30am.
|
| IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 15 Dec 2011 at 2:58am |
if not(weekday(CDate ({TicketDateTime}))) in ['1','7'] then
timevalue({TikALL.TicketDateTime})>=timeserial(17,30,0) or
timevalue({TikALL.TicketDateTime})<=timeserial(7,30,0)
|
| IP Logged |
|
barnwpk
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 15 Dec 2011 at 3:21am |
I get an error "boolean is required" on the highlighted section below.
if not(weekday(CDate({TikALL.TicketDateTime})))in['1','7']then
timevalue({TikALL.TicketDateTime})>=timeserial(17,30,0) or
timevalue({TikALL.TicketDateTime})<=timeserial(7,30,0)
|
| IP Logged |
|
barnwpk
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 15 Dec 2011 at 6:09am |
This statement runs without error, but doesn't bring back info for the weekends
if not (weekday ({TikALL.TicketDateTime}) in [1,7]) then
timevalue({TikALL.TicketDateTime})>=timeserial(17,30,0) or
timevalue({TikALL.TicketDateTime})<=timeserial(7,30,0)
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Dec 2011 at 6:22am |
maybe this...
{TikALL.TicketDateTime} in lastfullmonth
and
(
(weekday ({TikALL.TicketDateTime}) in [1,7])
or
(time({TikALL.TicketDateTime})>=time(17,30,0) or
time({TikALL.TicketDateTime})<=time(7,30,0) )
)
|
| IP Logged |
|
barnwpk
Newbie
Joined: 14 Dec 2011
Location: United States
Online Status: Offline
Posts: 9
|
Posted: 15 Dec 2011 at 7:29am |
|
yes..That worked perfectly. Thank you so much.
|
| IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 15 Dec 2011 at 8:48am |
|
Oops, I should have checked before submitting, sorry, thanks DBLANK
|
| IP Logged |
|
|
|
